# Reporting Tag Historian Query Max daily value

**URL:** <https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477>\
**Category:** Ignition\
**Created:** [June 9, 2020, 11:44pm UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477 "2020-06-09T23:44:14Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Craighunter1986](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@Craighunter1986](https://forum.inductiveautomation.com/u/Craighunter1986)\
**Post date:** [June 9, 2020, 11:44pm UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/1 "2020-06-09T23:44:14Z")

</div>

I am having some trouble getting the information I am looking for from a report.

I have a tag historian query providing a 10min time-weighted average from a group of tags. simplified version pictured below:

![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d059307e7d0a95340660601702784044125764f8.png)

I would like to report the maximum value per day from the 10 min results. From reading other threads I think I might be required to create a query using a script. so far I have been able to return the maximum value from the entire dataset but I require the max of the 10min results each day, for any given date range.

```auto
Header = ['date','val']
		NewDataset = []
		
		rawDataset = data['DailyAverage10min'].getCoreResults()
		
		for row in range(rawDataset.rowCount):
			date = rawDataset.getValueAt(row,'t_stamp')
			val = max(rawDataset.getColumnAsList(rawDataset.getColumnIndex("val")))
			
			NewDataset.append([date, val])
			
		NewDataset = system.dataset.toDataSet(Header, NewDataset)
		
		data ['NewDailyAverage10min'] = NewDataset
```

any advice appreciated.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [June 10, 2020, 10:57am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/2 "2020-06-10T10:57:45Z")

</div>

You could definitely handle this in a SQL query, however then you’ll need to handle unioning of the partition tables.

Using what you’ve already got, you could do something like…

```auto
dailyMaxValues = {}
for row in range(rawDataset.rowCount):
	date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
	val = rawDataset.getValueAt(row,'val')
	
	dailyMaxValues[date] = max(dailyMaxValues.get(date), val)

ds_header = ['date', 'val']	
ds_data = []
for key in dailyMaxValues.keys():
	ds_data.append([key, dailyMaxValues[key]])

ds = system.dataset.toDataSet(ds_header, ds_data)

```

I’m not sure if system.date.format works with t\_stamps stored as epoch dates…

Edit: I edited the script as I was overwriting the original `data` variable, as discussed in replies below

---

<div class="post-metadata">

**Author:** ![Craighunter1986](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@Craighunter1986](https://forum.inductiveautomation.com/u/Craighunter1986)\
**Post date:** [June 10, 2020, 11:35am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/3 "2020-06-10T11:35:57Z")

</div>

i have given this a try but getting the following error:

WARN: Error invoking script.Traceback (most recent call last):  
File “function:updateData”, line 16, in updateData  
TypeError: list indices must be integers

```auto
	dailyMaxValues = {}
	rawDataset = data['DailyAverage10min'].getCoreResults()
	for row in range(rawDataset.rowCount):
		date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
		val = rawDataset.getValueAt(row,'val')
		
		dailyMaxValues[date] = max(dailyMaxValues.get(date), val)
	
	header = ['date', 'val']	
	data = []
	for key in dailyMaxValues.keys():
		data.append([key, dailyMaxValues[key]])
	
	ds = system.dataset.toDataSet(header, data)
	data ['NewDailyAverage10min'] = data
```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [June 10, 2020, 11:40am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/4 "2020-06-10T11:40:09Z")

</div>

I don’t have your whole code, what is line 16?

---

<div class="post-metadata">

**Author:** ![Craighunter1986](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@Craighunter1986](https://forum.inductiveautomation.com/u/Craighunter1986)\
**Post date:** [June 10, 2020, 11:40am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/5 "2020-06-10T11:40:56Z")

</div>

> [@Craighunter1986](#):
>
> data ['NewDailyAverage10min'] = data

sorry that is the last line:

```auto
data ['NewDailyAverage10min'] = data
```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [June 10, 2020, 11:43am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/6 "2020-06-10T11:43:03Z")

</div>

Ah, I overwrote that variable, `data`, and used it to store the data to create the new dataset. You’ll need to rename one of them.  
Maybe edit it to this (i’ll fix it in my other post as well for posterity):

```auto
	ds_data = []
	for key in dailyMaxValues.keys():
		ds_data.append([key, dailyMaxValues[key]])
	ds = system.dataset.toDataSet(header, ds_data)

```

---

<div class="post-metadata">

**Author:** ![Craighunter1986](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@Craighunter1986](https://forum.inductiveautomation.com/u/Craighunter1986)\
**Post date:** [June 10, 2020, 11:51am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/7 "2020-06-10T11:51:28Z")

</div>

Amazing that seems to work, thanks for your help it!

---

<div class="post-metadata">

**Author:** ![Craighunter1986](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@Craighunter1986](https://forum.inductiveautomation.com/u/Craighunter1986)\
**Post date:** [June 11, 2020, 7:41am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/8 "2020-06-11T07:41:32Z")

</div>

following on, is it possible to achieve the same with daily average? I hoped that

```auto
dailyMaxValues[date] = max(dailyMaxValues.get(date), val)
```

could have become:

```auto
dailyAveValues[date] = AVE(dailyAveValues.get(date), val)
```

but alas it is not that simple.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [June 11, 2020, 10:24am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/9 "2020-06-11T10:24:19Z")

</div>

Python 2.7 doesn’t have an average function (that I know of). 3 does, but simply replacing ‘max’ with ‘mean’ would take the average each time of the last average value and the new value; it won’t get you the average of all of the daily values.

You need to do something like this (untested):

```auto
	dailyAvgValues = {}
	dailyAvgValCount = {}
	rawDataset = data['DailyAverage10min'].getCoreResults()
	for row in range(rawDataset.rowCount):
		date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
		val = rawDataset.getValueAt(row,'val')
		
		dailyAvgValues[date] = dailyAvgValues.get(date) or 0 + val
		# keep track of how many values we've summed together
		dailyAvgValCount[date] = dailyAvgValCount.get(date) or 0 + 1
	
	for key in dailyAvgValues.keys():
		# divide the summated total daily values by the count of values summated, giving the average daily value
		dailyAvgValues[key] = dailyAvgValues[key]/dailyAvgValCount[key]
	
	header = ['date', 'val']	
	data = []
	for key in dailyAvgValues.keys():
		data.append([key, dailyAvgValues[key]])
	
	ds = system.dataset.toDataSet(header, data)
	data ['NewDailyAverage10min'] = data

```

---

<div class="post-metadata">

**Author:** ![Craighunter1986](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@Craighunter1986](https://forum.inductiveautomation.com/u/Craighunter1986)\
**Post date:** [June 11, 2020, 1:58pm UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/10 "2020-06-11T13:58:41Z")

</div>

The code compiles but the numbers don’t seem quite right.

I adjusted the time interval on the raw data set to make it easier to check the Ave result against the time interval raw. It looks like the average result is showing the first value of each date.

![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/40d7f840f1f18c2d9a5aac526e19f3cbe84afc80.png)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [June 11, 2020, 9:23pm UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/11 "2020-06-11T21:23:05Z")

</div>

Try adding some print statements to check the value of the two dictionaries within the first for loop and see if the avgvalues dict is correctly adding the new val to the previous total for the right day. Probably print the date and val as well:

```auto
	dailyAvgValues = {}
	dailyAvgValCount = {}
	rawDataset = data['DailyAverage10min'].getCoreResults()
	for row in range(rawDataset.rowCount):
		date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
		val = rawDataset.getValueAt(row,'val')
		
		dailyAvgValues[date] = dailyAvgValues.get(date) or 0 + val
		# keep track of how many values we've summed together
		dailyAvgValCount[date] = dailyAvgValCount.get(date) or 0 + 1

	print ",".join([date, val, dailyAvgValues[date], dailyAvgValCount[date]])

```

---

<div class="post-metadata">

**Author:** ![Craighunter1986](https://avatars.discourse-cdn.com/v4/letter/c/bbe5ce/32.png) [@Craighunter1986](https://forum.inductiveautomation.com/u/Craighunter1986)\
**Post date:** [June 12, 2020, 10:12am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/12 "2020-06-12T10:12:30Z")

</div>

It doesn’t like the join statement.

WARN: Error invoking script.Traceback (most recent call last):  
File “function:updateData”, line 13, in updateData  
TypeError: sequence item 1: expected string or Unicode, float found

Where can you view the print statements? I tried to use the Script Console but but as this is using datasets from reports, I couldn’t get it to work.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [June 23, 2020, 11:19am UTC](https://forum.inductiveautomation.com/t/reporting-tag-historian-query-max-daily-value/35477/13 "2020-06-23T11:19:56Z")

</div>

Yep my bad, I relied on implicit order of operations and got it wrong…  
See the two EDIT comments for changes. Also, I created a dummy dataset at the start

```auto
dailyAvgValues = {}
dailyAvgValCount = {}
rawDataset = [[system.date.parse('2020-06-20 00:00:00'), 1]]
rawDataset.append([system.date.parse('2020-06-20 00:5:00'), 3])
rawDataset.append([system.date.parse('2020-06-21 00:00:00'), 4])
rawDataset.append([system.date.parse('2020-06-21 00:04:00'), 3])

rawDataset = system.dataset.toDataSet(['t_stamp', 'val'],rawDataset)

for row in range(rawDataset.rowCount):
	date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
	val = rawDataset.getValueAt(row,'val')
	
	dailyAvgValues[date] = (dailyAvgValues.get(date) or 0) + val ## EDIT: ADDED BRACKETS
	
	# keep track of how many values we've summed together
	dailyAvgValCount[date] = (dailyAvgValCount.get(date) or 0) + 1.0 ## EDIT: ADDED BRACKETS AND CHANGED FROM 1 TO 1.0 so that decimal averages are shown if values are integers, otherwise results are floored
for key in dailyAvgValues.keys():
	# divide the summated total daily values by the count of values summated, giving the average daily value
	print dailyAvgValues[key],dailyAvgValCount[key],dailyAvgValues[key]/dailyAvgValCount[key]
	dailyAvgValues[key] = dailyAvgValues[key]/dailyAvgValCount[key]

header = ['date', 'val']	
data = []
for key in dailyAvgValues.keys():
	data.append([key, dailyAvgValues[key]])

ds = system.dataset.toDataSet(header, data)
print data

```