# How to get the stored data on a custom variable when using Custom Aggregate function **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [August 29, 2023, 6:31am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492 "2023-08-29T06:31:44Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 29, 2023, 6:31am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/1 "2023-08-29T06:31:44Z")
How to I get access to the `cnt` value used in this blockContext when the query result is returned. ```auto def myCount(qval, interpolated, finished, blockContext, queryContext): cnt = blockContext.getOrDefault('cnt',0) if qval.quality.isGood(): blockContext['cnt']=int(cnt)+1 if finished: return blockContext.getOrDefault('cnt', 0) ``` ```auto data=system.tag.queryTagHistory( paths=paths, aggregationModes =['shared.myCount'], startDate=start, endDate=end, returnSize=1000 ) ``` The reason I am asking is because I have a Totalizer which I want to use ` Range` Aggregation to get the day total. The problem is , there are zeros in the data which cause the return value to be incorrect. I want to create a custom aggregation function to remove 0's from the return. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 29, 2023, 10:15am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/2 "2023-08-29T10:15:44Z")
I don't think you can pass your own functions to `aggregationMode`. It might be simpler to post-process the un-aggregated results. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 30, 2023, 3:25pm UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/3 "2023-08-30T15:25:22Z")
> [@pascal.fragnoud](#): > > I don't think you can pass your own functions to `aggregationMode`. You can. There are some restrictions though. [https://docs.inductiveautomation.com/display/DOC81/Custom+Tag+History+Aggregates](https://docs.inductiveautomation.com/display/DOC81/Custom+Tag+History+Aggregates) > [@liam1](#): > > The reason I am asking is because I have a Totalizer which I want to use ` Range` Aggregation to get the day total. The problem is , there are zeros in the data which cause the return value to be incorrect. You would just return the value once finished is true. If you chose to use a custom aggregate then you have to provide the logic to process the values. You can't combine that with a pre-built aggregate. ```python def myCount(qval, interpolated, finished, blockContext, queryContext): cnt = blockContext.getOrDefault('cnt',0) if qval.quality.isGood(): cnt += 1 blockContext['cnt'] = cnt if finished: return cnt ``` Here is an example of of an aggregate that I use to calculate the duration based on particular values of an integer tag. ```python def customStatusDuration(qval,interpolated,finished,blockContext,queryContext): lastValue = queryContext.get('lastValue',False) startTime = blockContext.get('startTime',None) duration = blockContext.get('duration',0) if all([qval.value == 0,not lastValue,qval.quality.good]): startTime = qval.timestamp elif all([startTime,qval.value,qval.quality.good,not finished]): duration += system.date.secondsBetween(startTime,qval.timestamp) #clear the start time so that we don't add time in between stops to the #duration. startTime = None queryContext['lastValue'] = qval.value == 0 if finished: if startTime is not None: duration += system.date.secondsBetween(startTime,system.date.fromMillis(blockContext.blockEnd)) return duration blockContext['duration'] = duration blockContext['startTime'] = startTime ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 30, 2023, 3:36pm UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/4 "2023-08-30T15:36:46Z")
Then shouldn't the count be stored in the `queryContext` instead of the `blockContext` ? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [August 30, 2023, 3:40pm UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/5 "2023-08-30T15:40:14Z")
That depends on if the value needs to be persisted across multiple blocks. For instance, in my example I persist the lastValue across the query, so that the next block knows what the last value of the previous block was, but I don't for the duration or startTime. ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 5:41am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/6 "2023-08-31T05:41:39Z")
Is it possible to remove/skip a block from being return? Lets say I'm screening for blocks where the value is zero and do not want to have it returned in the final dataset, how would I skip it? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 31, 2023, 7:03am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/7 "2023-08-31T07:03:02Z")
You can't not return from a python function - even if you don't return explicitly, it returns `None`. Well, you _could_ not return, by raising an error, but I doubt that helps here. What exactly do you want your aggregation function to do ? Or maybe a better question would be: What do you want `queryHistory` to return ? ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 7:23am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/8 "2023-08-31T07:23:21Z")
I want the aggregation function to remove zero values from the return dataset. Lets say the tag history is as follows, For simplicity sake here is an example of a return from a normal queryTagHistory ```auto [ ["01-01-2023":1], ["02-01-2023":2], ["03-01-2023":3], ["04-01-2023":0], ["04-01-2023":4], ["05-01-2023":5] ] ``` This is where I want the aggregation function to remove the zero values from the dataset and return this instead. ```auto [ ["01-01-2023":1], ["02-01-2023":2], ["03-01-2023":3], ["04-01-2023":4], ["05-01-2023":5] ] ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 31, 2023, 7:24am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/9 "2023-08-31T07:24:55Z")
But what are the other values ? The exact data points ? You were talking about range and a totalizer, so there must be more going on than just removing zeros ? ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 7:27am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/10 "2023-08-31T07:27:06Z")
Accidently post the reply before I was finished typing. ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 7:32am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/11 "2023-08-31T07:32:24Z")
These would be flow totals, I want to calculate the Flow Total for a specified time range. Where the Flow total is `Latest Flow Total - Earliest Flow Total=Total Flow for that period of time`. The issue is , with large amounts of data I would have to run another loop after retrieving the data from the tag history query to remove the 0's from the dataset. I am hoping to use the custom aggregation function to do this when the data is being retrieved. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 31, 2023, 7:38am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/12 "2023-08-31T07:38:36Z")
Frankly, just filter the returned dataset. This is simpler, clearer, and easier to do. **IF** it's not fast enough, **THEN** turn to custom aggregation. ```python ds = system.tag.queryHistory(...) ds = system.dataset.toDataSet( list(ds.columnNames), filter(lambda x: x['value_column'] != 0, system.dataset.toPyDataSet(ds)) ) ``` I can't see a way to skip a block in the aggregation function, except maybe if you were storing valid block data in the `queryContext` and returning that at the end, but that feels a bit... weird, and the return of `queryHistory` would have to be re-processed to get what you really need. ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 7:47am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/13 "2023-08-31T07:47:26Z")
The period of time can be as long as a year , meaning if I can avoid looping through the data multiple times if would be nice. The quick fix I have implemented, is to check if the value at index -1 and 0 ,if the value at the end of the array is equal to 0, I walk backwards (forwards for index 0) till I find a non-zero value . ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 31, 2023, 7:49am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/14 "2023-08-31T07:49:21Z")
What ? Where are you doing this ? I fail to see how that solves anything... ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 7:58am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/15 "2023-08-31T07:58:19Z")
Here is an example of why I am doing this. ```auto data=[ ["01-01-2023":0], ["02-01-2023":2], ["03-01-2023":3], ["04-01-2023":0], ["04-01-2023":4], ["05-01-2023":5] ] ``` To calculate the Flow Total I would do this ```auto Total=data[-1][1] - data[0][1] Total = 5 - 1 Total = 4 ``` Which is wrong. Using this method I described, this is what I am doing. ```auto data=[ ["01-01-2023":0],<--check if data[0][1] is 0 , if so move to the next index ["02-01-2023":2], ["03-01-2023":3], ["04-01-2023":0], ["04-01-2023":4], ["05-01-2023":5] <--check if data[-1][1] is 0 ,if not use this as end value ] ``` ```auto data=[ ["01-01-2023":0], ["02-01-2023":2],<--check if data[0][1] is 0 , if not use this as end value ["03-01-2023":3], ["04-01-2023":0], ["04-01-2023":4], ["05-01-2023":5] <--check if data[-1][1] is 0 ,if not use this as end value ] ``` ```auto Total=endValue - startValue Total = 5 - 2 Total = 3 ``` Which is correct. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 31, 2023, 8:01am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/16 "2023-08-31T08:01:51Z")
So, what you actually want, is not to remove the 0, but calculate a value that uses the first non-zero value and the last non-zero value ? This seems like a totally different thing to do in an aggregation function ! Store the first and last non-zero values in the query context, then when you reach the end of the query, return the difference between the 2. ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 8:02am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/17 "2023-08-31T08:02:45Z")
If the data was ""cleaned" and I expect no 0 values at the start or end of the array (assume there are totals for the start and end range) , I don't have to perform an extra step to do this check. Unfortunately for some reason the totalizer tag has store random 0's in the database. ---
**Author:** ![liam1](https://avatars.discourse-cdn.com/v4/letter/l/e68b1a/32.png) [@liam1](https://forum.inductiveautomation.com/u/liam1)\ **Post date:** [August 31, 2023, 8:05am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/18 "2023-08-31T08:05:39Z")
How do you know if your last value in the query when using custom aggregation? Is there a array length value and index position provided in the queryContext? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [August 31, 2023, 8:07am UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/19 "2023-08-31T08:07:40Z")
I guess you can use the `blockEnd` key of the block context and compare it to the `queryEnd` key of the query context. Or keep doing what you're doing now, which is honestly not a bad solution. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 31, 2023, 2:16pm UTC](https://forum.inductiveautomation.com/t/how-to-get-the-stored-data-on-a-custom-variable-when-using-custom-aggregate-function/78492/20 "2023-08-31T14:16:39Z")
Sounds to me like you shouldn't be using the tag historian, but transaction groups. Then use SQL lead() or lag() outside a subquery that discards the zeros.