Rollup Value Calculations and No Values for 15m intervals

[cbyrne]

I've got a question in two parts. From looking at some delta data, there seems to be some discrepancy with rollups. This may be a misunderstanding on my part to how the deltas/rollups are calculated. I'm also getting no graph for 15m intervals.

---

Part 1

Sample data for a power usage accumulator:

timestamp               value
21/8/2019 05:30:00 	2724.95 kW·h
21/8/2019 05:15:00 	2722.89 kW·h
21/8/2019 05:00:00 	2720.82 kW·h
21/8/2019 04:45:00 	2718.79 kW·h
21/8/2019 04:30:00 	2717.55 kW·h
21/8/2019 04:15:00 	2716.30 kW·h
21/8/2019 04:00:00 	2715.06 kW·h
21/8/2019 03:45:00 	2713.82 kW·h
21/8/2019 03:30:00 	2712.57 kW·h
21/8/2019 03:15:00 	2711.34 kW·h
21/8/2019 03:00:00 	2710.09 kW·h
21/8/2019 02:45:00 	2708.84 kW·h
21/8/2019 02:30:00 	2707.59 kW·h
21/8/2019 02:15:00 	2706.36 kW·h
21/8/2019 02:00:00 	2704.24 kW·h
21/8/2019 01:45:00 	2702.09 kW·h
21/8/2019 01:30:00 	2699.94 kW·h

If we set the rollup interval to 15m (or 900 seconds) and rollup type to delta, the graph on the data point details page goes blank:

Bug or expected behaviour?

---

Part 2:

For a 30 minute rollup and 60 minute rollup the graphs are as follows:

and the transcribed values are:

time	30 mins	60 mins
02:00	2.12	4.6
02:30	1.25
03:00	1.24	3.73
03:30	1.25
04:00	1.24	3.73
04:30	1.24

I would have assumed that the 1 hour delta is the assume as the combined 30 minute deltas but it seems not to be the case.

[phildunlap]

Hi cbyrne,

I would expect most of this is being caused by the precision of everything occurring exactly on the milliseconds of the 15 minute intervals. Mango statistics are computed with an inclusive period start time, but an exclusive period end time. So, with the fifteen minute data, or really any of the rollups you are trying that are multiples of that, can have this behavior in the delta. As far as the statistics are concerned, the 15 minutes started with a real value, then no other values occurred in the next 15 minutes, so the delta is zero.

In the 30 minute example, there are two values in the period, so the delta can be computed, but because the period ends exactly when the next value is recorded, the delta between those two values is not accounted for in either period's delta.

Try offsetting your periods by a millisecond - except be aware that doing rollups via the datebar will automatically align the data period to fit an integer number of rollup periods inside the data period by expanding start and end times to period boundaries. With REST requests this can be controlled via setting the truncate parameter false.

[cbyrne]

Hi @phildunlap, thanks for the info, however I think this way of calculating deltas is flawed for anyone doing metered readings. These happen at regular intervals like above and currently the delta value for an hour does not show the user their usage for one hour and this is the critical issue.

The way the delta is being calculated effectively drops one reading per rollup period and gives a misleading result. The normal approach for metered data at regular intervals is to only allow rollups that are a multiple of the reading interval and to calculate the delta based on the difference between the first reading of each rollup period i.e. for 15 minute readings, an hourly rollup would take the first reading of hour 2 and subtract the first reading of hour 1.

Ofsetting by 1 ms does not cure the problem as it forces the last reading in the period into the next period so it still gets missed.

One solution might be to add a "consumption" delta option to use this method? We're trying to push Mango to our customers right now for energy monitoring and this is the behaviour they need and expect. Similarly, would you consider adding a last_value-first value to the mango statistics object? Again, for energy monitoring, this delta is the key piece of information as it's their usage in the time period.

[cwangv]

@cbyrne
I am just wondering what the polling intervals you have got for your points? I can imaging if the polling interval is the same as history interval, you may run
into such issues.

[cbyrne]

@cwangv Yeah our polling interval is every 15 minutes.

[cwangv]

@cbyrne
that may well be the cause of your issue. Due to polling delay, the data will not come in at exact 15 minutes intervals.
you need to have shorter poll interval period (which is different to your history log interval), eg, 5 minutes, then over 15 minutes period you will have 3 samples in the poll, when history log kicks in, there will always be data there to collect even it is maybe from 5 minutes ago.
Give it a try.

[cbyrne]

@cwangv
We import our data from CSV files that are timestamp,value_1...value_n in fomat.
Maybe I'm misunderstanding but I don't see polling times affecting it.

[phildunlap]

cwangv

that may well be the cause of your issue. Due to polling delay, the data will not come in at exact 15 minutes intervals.

Not so! Mango is somewhat deceptive in terms or recorded timestamps in polls, which is why we discourage collecting highly time-precise data directly via Mango.

Mango will schedule a poll for a data source at a certain time, and all data will report that timestamp for the poll, even if the poll took quite some time to run. That's not true for every type of data source, since some transmit the timestamps with the data, but for protocols like BACnet and Modbus all points sampled in the poll will have the poll as the timestamp of the value.

The problem is somewhat more readily evident even with those sources now, since "Quantize" became an option on all data sources, which will align the period along the millisecond boundary of the polling period, so it is easy to collect data with these timestamps.

cbyrne

Hi @phildunlap, thanks for the info, however I think this way of calculating deltas is flawed for anyone doing metered readings. These happen at regular intervals like above and currently the delta value for an hour does not show the user their usage for one hour and this is the critical issue.

I can see your argument. I will raise it with others and see their perspective. The reason the ending timestamp is exclusive is that otherwise values on the period boundary could be counted twice in separate periods, causing the sum, average, and integral to have issues when comparing to the whole period. But, as you are noting, the delta has that behavior.

Ofsetting by 1 ms does not cure the problem as it forces the last reading in the period into the next period so it still gets missed.

Offsetting the values by 1 ms backward would solve the display issue of those rollups, I would expect. Or one could offset the period 1 ms forward if not using the datebar and truncate is false.

Similarly, would you consider adding a last_value-first value to the mango statistics object?

There already are the values Start, First, and Last in terms of values. They are formally defined here: https://help.infiniteautomation.com/mango-rollups-and-statistics

[cwangv]

@cbyrne
I didn't know that your data was imported. That all make sense now together with how Mango handles the data that falls on the boundary time.
This is definitely a scenario I need to watch out for.
thanks.

[cbyrne]

@phildunlap Thanks Phil, much appreciated!

Similarly, would you consider adding a last_value-first value to the mango statistics object?

There already are the values Start, First, and Last in terms of values. They are formally defined here: https://help.infiniteautomation.com/mango-rollups-and-statistics

I should have been more specific on what I meant, I mean could the statistics object include a value which is equal to the last value minus the start value for the time period, essentially, the consumption or delta in my terms. For example, on the point details page in the point statistics card, it would be great to have one more value, the delta/consumption. But this is minor to the original issue.

[phildunlap]

The last value is the the value at the period end time, since that is exclusive.

I think what will probably happen is that the delta will be computed with an inclusive period end time, while all the other statistics will remain computed with an exclusive period end time, but I haven't collected that consensus yet.

[cbyrne]

@phildunlap Cool cool cool, sounds like a nice solution. Thanks for keeping us in the loop!

[phildunlap]

We had a discussion and this is the git issue: https://github.com/infiniteautomation/ma-core-public/issues/1480

We decided that it was a better solution to include the value prior to the period start time for computing the delta, since the same at the period start would be the instant of change, not the period where the prior value was held.