Notes About History Rollups

The following notes and examples provide insight into how rollups work with offsets and missing values. See History Rollups for a definition of the parameters described in these examples.

Offset Notes

The RollupPeriod for a RollupType of Calculated Weighted Average tells the iterator how many days to include in each instance of the RollupUnit. If the RollupUnit is Days and the RollupPeriod is 2, there will be an entry for each day with the Calculated Weighted Average of the 2 days previous to that day rolled into the calculation.

The Offset for Calculated Weighted Average tells the iterator where to start iterating. For example:

If you have an EarliestTime of 6/5/2024 and a LatestTime of 6/7/2024 and your Offset is 12, the iteration will start on 6/4/2024 at noon and continue through 6/7/2024 at noon and you will have 4 rollup values:

6/4/2024 12:00:00.000

6/5/2024 12:00:00.000

6/6/2024 12:00:00.000

6/7/2024 12:00:00.000

If you have an EarliestTime of 6/5/2024 and an LatestTime of 6/7/2024 and your Offset is 0 (midnight), the iteration will begin on 6/4/2024 at midnight and roll up the first day (6/4/2024 - 6/5/2024) and continue on through 6/7/2024 at midnight and you will have 3 rollup values:

6/5/2024 00:00:00.000

6/6/2024 00:00:00.000

6/7/2024 00:00:00.000

For all the rollups, the EarliestTime is not where the rollups start, but where the first rollup ends at — an EarliestTime of 6/5/2024 with an Offset of 0 means that the first rollup will go from 6/4/2024 00:00:00.000 to 6/5/2024 00:00:00.000.

The Offset DOES affect how many rollups appear for any given rollup. If you give an Offset of 9 and an EarliestTime of 6/5/2024 00:00:00.000 with a RollupPeriod of 1 day the iterator will try to match the times up by starting from 6/3/2024 09:00:00.000 - 6/4/2024 09:00:00.000 for the first rollup.

It does this because the 9 comes before midnight and it is trying to rollup to 9 so it has to go one extra day back. Think of it in terms of a timeline:

0__1__2__3__4__5__6__7__8__9__10__11__12__13__14__15__16__17__18__19__20__21__22__23__0

6/5/2024__________________________________ 6/6/2024

To roll up to 9 given an EarliestTime of 6/5/2024 00:00:00.000, the iterator goes back to the previous 9, which is 6/4/2024 09:00:00.000 and uses that as the start time. This means that it has to go back to 6/3/2024 09:00:00.000 and roll forward to 6/4/2024 09:00:00.000 for the first rollup value.

Rules of EarliestTime (Start Time) and LatestTime (End Time)

If the LatestTime extends beyond the Offset, then the rollup will end the day after the LatestTime.
If the EarliestTime comes before the Offset, then the rollup will start two days before the EarliestTime and the first rollup will end the day before the EarliestTime.
If the LatestTime ends before the Offset, then the rollup will end at the Offset on the LatestTime date.
If the EarliestTime extends beyond the Offset, then the rollup will start exactly one day before the EarliestTime and the first rollup will end at the Offset on the EarliestTime date.

Example 1 — When to Start, When to End

If you change the EarliestTime or LatestTime it could cause the rollups to go back further to start the rollup than you might expect depending on the Offset. This is because the EarliestTime is set as the end of the first RollupPeriod and if the end of the first RollupPeriod is not in line with the Offset then it could cause the calculation to go back further to look for a proper beginning point — this would mean, for a rollup with the following parameters:

	Parameters	Values
	RollupType:	Calculated Weighted Average
	EarliestTime:	6/11/2024 15:59:59.999
	LatestTime:	6/12/2024 15:59:59.999
	Offset:	15 (3PM)
	RollupUnit:	Days
	RollupPeriod:	1

The rollup will look at the Offset and time range and these will be the results:

The first rollup should end on 6/11/2024 15:00:00.000 and the EarliestTime is 6/11/2024 15:59:59.999.

6/11/2024 15:59:59.999 comes after 6/11/2024 15:00:00.000

There are more than 24 hours between (EarliestTime – (minus) 24 hours) and the EarliestTime so the first rollup will end on 6/11/2024 15:00:00.000. The second rollup will be from 6/11/2024 15:00:00.000 to 6/12/2024 15:00:00.00 with the LatestTime being after the Offset (15 is the unit and 15:59:59.999 is the time to stop). This means the rollup will go another record into the future and rollup from 6/12/2024 15:00:00.000 to 6/13/2024 15:00:00.000. The three records will then be:

6/11/2024 15:00:00.000

6/12/2024 15:00:00.000

6/13/2024 15:00:00.000

Example 2 — Insufficient Data at Start Time

This example uses the following parameters:

	Parameters	Values
	RollupType:	Calculated Weighted Average
	EarliestTime:	6/11/2024 15:59:59.999
	LatestTime:	6/12/2024 15:59:59.999
	Offset:	16 (4PM)
	RollupUnit:	Days
	RollupPeriod:	1

The rollup will look at the Offset and time range and these will be the results:

The first rollup should end on 6/11/2024 16:00:00.000 and the EarliestTime is 6/11/2024 15:59:59.999.

6/11/2024 15:59:59.999 comes before 6/11/2024 16:00:00.000

There are fewer than 24 hours between (EarliestTime – (minus) 24 hours) and EarliestTime so the first rollup will end on 6/10/2024 16:00:00.000 because it has to go back an extra day to be able to rollup 24 hours’ worth of data and end right at the 16:00:00.000 mark. The second rollup will be from 6/10/2024 16:00:00.000 to 6/11/2024 16:00:00.00. The LatestTime being before the Offset (16 is unit and 15:59:59.999 is the time to stop). This means that the rollup will stop at the LatestTime just as you want it to, that is: 6/12/2024 16:00:00.000. The three records will then be:

6/10/2024 16:00:00.000

6/11/2024 16:00:00.000

6/12/2024 16:00:00.000

Example 3 — Missing Value at EarliestTime

When the VHS goes to rollup values from a specific point in time to another specific point in time it uses a Hard Window to force a value to exist at the EarliestTime even if no value actually exist at the earliest time. The VHS carries over the last reported value at the time, and the iterator starts at exactly the point where EarliestTime specifies, taking into account the rules described above.

It is very important to remember that, the value found exactly at the EarliestTime will be included even if no history entry exists at that time. This means that for a RollupType such as Calculated Mean this will add one to the denominator.

For example, assume we have the following raw values:

	Value	Date and Time
	1	01/13/2024 08:01:00.000
	5	01/13/2024 09:12:23.000
	10	01/13/2024 10:12:23.000
	100	01/14/2024 08:12:23.000
	1000	01/14/2024 20:16:31.000

And the iterator uses the following parameters:

	Parameters	Values
	RollupType:	Calculated Mean
	EarliestTime:	01/14/2024
	LatestTime:	01/14/2024
	Offset:	9
	RollupUnit:	Days
	RollupPeriod:	1

We will see the following occur:

The Offset (9) is before the EarliestTime so the iterator will go back to 01/13/2024 09:00 and roll up to 01/14/2024 09:00 for the first rollup.
The values used will be: (1 + 5 + 10 + 100) / 4 = 29. The Calculated Mean for the first rollup will be 29.
This is because the VHS assumes the value at 01/13/2024 09:00:00.000 is 1, which was the last reported value at that time (from 08:01:00.000).
If you alter the Offset to 10, we will see the same issue where the value at 01/13/2024 09:12:23.000 is included in the calculation, even though it doesn't fit in the time window. This is because a value must exist at the EarliestTime for the rollup to work.
You might see issues where it looks like 25 hours are being rolled into a day, when, in actuality, the VHS just has to assume the value at EarliestTime (according to the rules above).

Thin Rollup Types

For Thin Rollup types (Thin Median, Thin Min/Max, and Thin Last), the iterator looks for values within each RollupPeriod. If no values are found within that RollupPeriod, the iterator thins the results by not rolling up that period. This results in RollupPeriods that are missing from the output.