Aggregate List (version 24)

Knowledge Base / Version 24 / Client Tools / Excel Add-in

updated 2 mths ago
5replies

Aggregates

According to the OPC Foundation, aggregates "are methods that summarize data values. Common aggregates include 'average over a given time range', 'minimum over a time range', and 'maximum over a time range'. These aggregates are performed during the retrieval of the data."

The aggregates that Canary supports are defined and maintained by the OPC Foundation. In the list of aggregates, you will notice some have been updated with a second version, represented by the number '2'. These indicate a change in the definition, typically resulting around whether the aggregate uses simple or interpolated bounding values.

Simple Bounding Value - uses the data point immediately before the timestamp to estimate the bounding value if no value exists on the timestamp, even if this point carries a 'Bad' data quality

Interpolated Bounding Value - uses the first non 'Bad' quality data point found before the timestamp to estimate the bounding value if no value exists on the timestamp

With the exception of the Interpolative aggregate which uses Sloped Interpolation to estimate the bounding value, Canary uses Stepped Interpolation to estimate the bounding value for each of its aggregates. See https://reference.opcfoundation.org/Core/Part13/v104/docs/3.1 for more detailed information on how bounding values are calculated and the difference between Sloped and Stepped Interpolation.

The following are the aggregates currently supported by the Canary Excel Add-in and the OPC Foundation. This list can be found within the Excel Add-in ribbon bar within the General info section as well.

Aggregate Name	Definition
Average	Retrieve the average value of the data over the interval.
Count	Retrieve the number of raw values over the interval.
CountInStateNonZero	Retrieve the number of times a Boolean or numeric was in a non-zero state using Simple Bounding Values.
CountInStateZero	Retrieve the number of times a Boolean or numeric was in a zero state using Simple Bounding Values.
Delta	Retrieve the difference between the Start and End value in the interval.
DeltaBounds	Retrieve the difference between the StartBound and EndBound value in the interval.
DeltaTotalCount	Retrieve the total delta of the values for an accumulation tag that resets periodically.
DurationBad	Retrieve the total duration of time (milliseconds) in the interval during which the data is Bad.
DurationGood	Retrieve the total duration of time (milliseconds) in the interval during which the data is Good.
DurationInStateNonZero	Retrieve the time (milliseconds) a Boolean or numeric was in a non-zero state using Simple Bounding Values.
DurationInStateZero	Retrieve the time (milliseconds) a Boolean or numeric was in a zero state using Simple Bounding Values.
End	Retrieve the last value in the interval.
EndBound	Retrieve the value at the end of the interval using Simple Bounding Values.
Instant	Retrieve the value at the exact beginning of the interval.
Interpolative	At the beginning of each interval, retrieve the calculated value from the data points on either side of the requested timestamp.
Maximum	Retrieve the maximum raw value in the interval with the timestamp of the start of the interval.
Maximum2	Retrieve the maximum value in the interval including the Simple Bounding Values.
MaximumActualTime	Retrieve the maximum value in the interval and the timestamp of the maximum value.
MaximumActualTime2	Retrieve the maximum value with the actual timestamp including the Simple Bounding Values.
Minimum	Retrieve the minimum raw value in the interval with the timestamp of the start of the interval.
Minimum2	Retrieve the minimum value in the interval including the Simple Bounding Values.
MinimumActualTime	Retrieve the minimum value in the interval and the timestamp of the minimum value.
MinimumActualTime2	Retrieve the minimum value with the actual timestamp including the Simple Bounding Values.
NumberOfTransitions	Retrieve the number of changes between zero and non-zero that a Boolean or numeric value experienced in the interval.
PercentBad	Retrieve the percentage of data (0 to 100) in the interval which has Bad StatusCode.
PercentGood	Retrieve the percentage of data (0 to 100) in the interval which has Good StatusCode
Range	Retrieve the difference between the minimum and maximum value over the interval.
Range2	Retrieve the difference between the Minimum2 and Maximum2 value over the interval.
StandardDeviationPopulation	Retrieve the standard deviation for the interval for a complete population (n) which includes Simple Bounding Values.
StandardDeviationSample	Retrieve the standard deviation for the interval for a sample of the population (n-1).
Start	Retrieve the first value in the interval.
StartBound	Retrieve the value at the beginning of the interval using Simple Bounding Values.
TimeAverage	Retrieve the time weighted average data over the interval using Interpolated Bounding Values.
TimeAverage2	Retrieve the time weighted average data over the interval using Simple Bounding Values.
Total	Retrieve the total (time integral) of the data over the interval using Interpolated Bounding Values.
Total2	Retrieve the total (time integral in seconds) of the data over the interval using Simple Bounding Values.
TotalPer24Hours	Retrieve the total (time integral in 24 hours) of the data over the interval using Simple Bounding Values.
TotalPerHour	Retrieve the total (time integral in hours) of the data over the interval using Simple Bounding Values.
TotalPerMinute	Retrieve the total (time integral in minutes) of the data over the interval using Simple Bounding Values.
VariancePopulation	Retrieve the variance for the interval as calculated by the StandardDeviationPopulation which includes Simple Bounding Values.
VarianceSample	Retrieve the variance for the interval as calculated by the StandardDeviationSample.
WorstQuality	Retrieve the worst StatusCode of data in the interval.
WorstQuality2	Retrieve the worst StatusCode of data in the interval including the Simple Bounding Values.

Excel Add-in

- patch_hyland
- 4 mths ago
- Reported - view
What unit of measure do the Duration and Time Aggregate values default to?
- smason
  
  4 mths ago
  
  Reported - view
  Patch Hyland Any of the Duration aggregates output milliseconds.
- patch_hyland
  
  4 mths ago
  
  Reported - view
  Thank you Steve Mason , it seems like it would be useful if it said that somewhere within the article on this page.
- smason
  
  4 mths ago
  
  Reported - view
  Patch Hyland Sounds like a good idea to me. I updated the descriptions to include the unit of measure.
- patch_hyland
  
  4 mths ago
  
  Reported - view
  Steve Mason Amazing, thank you!