# How to calculate moving-window values **URL:** \ **Category:** Ignition\ **Tags:** ignition79, scripting\ **Created:** [March 19, 2020, 3:41pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244 "2020-03-19T15:41:33Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![joshua.rowlison](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@joshua.rowlison](https://forum.inductiveautomation.com/u/joshua.rowlison)\ **Post date:** [March 19, 2020, 3:41pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/1 "2020-03-19T15:41:33Z")
Hello all, I’m gonna try and generalize my quesition so the answers will be useful for more than just me. The situation I’m facing is this: I have a machine, from which I can extract the current production count. I’ve enabled the historian on this tag, but I’m struggling to figure out how I can readily calculate the production rate for the timeframe in a way that doesn’t seem fragile, prone to poor performance and hard to comprehend for maintenance. The current version I’m looking at basically has a script querying the tag historian for data in the timeframe and then selecting the largest from the resulting dataset. Then it’s querying for the calculated minimum in the window and comparing the values to get production over the timeframe, then that value is divided by the number time-units in the time frame(e.g. minutes in the hour) to get production per unit for the timeframe. This script seems to be running on a timer in order to refresh regularly, Given fact that the count updates in near-realtime, it feels fundamentally wrong to be doing this chain of queries and slicing datasets in order to get such a basic value. I’m given to think I might be missing something, but so far I can’t find a great alternative. Even my attempts to condense the queries by looking for the maximum and minimum counts in the timeframe into one queryTagCalculations call, but the maximum value is significantly smaller than the highest value I get if I look up all the values in the timeframe and select the largest myself. So the question is basically this: How do you do this without running a hefty script on every change and/or tick on the timer? Can this value be calculated when the count updates, without being too heavy to safely run at that kind of speed? ---
**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:** [March 19, 2020, 4:58pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/2 "2020-03-19T16:58:38Z")
Consider scripting a FIFO of timestamps and values. Hold the FIFO in a list in your python script module for efficiency. On script reloads, preload your FIFO from the DB. Otherwise, when the tag changes or on some timer event, append to the FIFO list (timestamp and new value), and compute against the oldest entry. Remove the oldest from the list if it falls before your sliding window. If you don't want to script it, this post has a similar operation done with expression tags and functions from my free add-on module: > [@Tag Value Delta Calculation](http://forum.inductiveautomation.com/t/tag-value-delta-calculation/32221): > > Hello all, I am attempting to create a tag that will represent the change in an OPC tags value over the previous 60 seconds. I have flirted with a few ideas on how to do this, but have run into some potential issues with each version i tried. First, I attempted to just create an expression tag that is on a much slower scan class that takes the value of the OPC tag every 60 seconds (scan class execution). That way, there would be a tag that represents the current count value (OPC tag) as well a… ---
**Author:** ![David](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@David](https://forum.inductiveautomation.com/u/David)\ **Post date:** [March 19, 2020, 5:06pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/3 "2020-03-19T17:06:22Z")
I don’t have any advice for doing it within python scripts, but this is a common problem in database programming. SQL Server, Oracle (and likely Postgres) have windowing functions built in. Are you running on any of these? ---
**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:** [March 19, 2020, 5:09pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/4 "2020-03-19T17:09:29Z")
> [@David](#): > > and likely Postgres Oh, yes. I switched to Postgres ~15 years ago when they introduced window functions. Previously only available in Oracle. MySQL and MS SQL Server were both absurdly and disappointingly late to that party. I still do FIFO solutions for these sorts of problems to avoid hitting the DB unnecessarily. And it can be really hard to use windowing functions against Ignition's historian. ---
**Author:** ![David](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@David](https://forum.inductiveautomation.com/u/David)\ **Post date:** [March 19, 2020, 5:44pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/5 "2020-03-19T17:44:19Z")
> [@pturmel](#): > > And it can be really hard to use windowing functions against Ignition’s historian. It is really hard to do quite a number of things against Ignition's historian -- we rolled our own. ---
**Author:** ![joshua.rowlison](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@joshua.rowlison](https://forum.inductiveautomation.com/u/joshua.rowlison)\ **Post date:** [March 20, 2020, 1:10pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/6 "2020-03-20T13:10:28Z")
When you say FIFO, are you suggesting basically a stack of key-value pairs? Then just do timestamp lookups from that to calculate the production rate? ---
**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:** [March 20, 2020, 2:07pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/7 "2020-03-20T14:07:59Z")
Yes, but keep them in order. Insert new samples at the head. At every insert, pop off the tail (possibly multiple) to keep the oldest within the desired time window. Then do the delta math with head versus tail. (If the fifo will be very large, consider using jython’s **deque** datatype instead of list.) ---
**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:** [March 20, 2020, 2:17pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/8 "2020-03-20T14:17:01Z")
BTW, these sorts of analyses are easiest and most precise if the counters being monitored are odometers that are _ **never** _ reset. Then any quantity for a time period is simply the odometer value at the end of the period minus the odometer value at the start of the period. ---
**Author:** ![joshua.rowlison](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@joshua.rowlison](https://forum.inductiveautomation.com/u/joshua.rowlison)\ **Post date:** [March 25, 2020, 5:58pm UTC](https://forum.inductiveautomation.com/t/how-to-calculate-moving-window-values/33244/9 "2020-03-25T17:58:52Z")
Unfortunately based on the existing code, I’m suspecting that I can’t make such an assumption. The code to try and catch a reset is already in place