What are the descriptions of the aggregates available in the Canary Excel Add-in?

Access the list of Aggregates and Descriptions in the General Info section of the Canary Labs Ribbon Bar

Before diving into how to access the aggregate list, it might be important to talk about the definition of an Aggregate. According to the OPC Foundation, Aggregates "are methods that summarize data values. Common aggregates include Averages 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." 

A further point to discuss is what the difference is between Simple Bounding Values Vs. Interpolated Bounding Values. As we will soon see, many of the aggregates on our list will use either Simple or Interpolated Bounding Values. Therefore, it will be important to understand how each type of bounding value affects the aggregated data in the selected time range.

Simple Bounding Value - When the weighted average of data is determined by using the true starting and ending values of the time range.

Interpolated Bounding Value - When the weighted average of data is determined by using the interpolated starting and ending values of the time range. For example, you may have had a change in a value immediately prior to the time range in question, without any change happening during the selected time range. If you use the Interpolated Bounding rules, then you will be able to account for the average in the selected time range which includes the data on each side of the requested time range. This could shed better insight on what is truly happening with the data.

The Canary Labs Excel Add-in is accessible from the Canary Labs Ribbon Bar which is the far right ribbon bar in any new or existing Excel Workbook, as shown in the image below:

The aggregate list can be accessed from the "General Info" section of the Canary Labs Ribbon Bar:

1. TimeAverage2 - Retrieve the time weighted average of the data over interval using Simple Bounding Values
2. Interpolative - At the beginning of each interval, retrieve the calculated value from the data points on either side of the requested timestamp.
3. Average - Retrieve the average value of the data over the interval
4. TimeAverage - Retrieve the time weighted average data over the interval using Interpolated Bounding Values
5. Total - Retrieve the total (time integral) of the data over the interval using Interpolated Bounding Values.
6. Total2 - Retrieve the total (time integral) of the data over the interval using Simple Bounding Values.
7. Minimum - Retrieve the minimum raw value in the interval with the timestamp of the start interval.
8. Maximum - Retrieve the maximum raw value in the interval with the timestamp of the start interval.
9. MinimumActualTime - Retrieve the minimum value in the interval and the timestamp of the minimum value.
10. MaximumActualTime - Retrieve the maximum value in the interval and the timestamp of the maximum value.
11. Range - Retrieve the difference between the minimum and maximum value over the interval
12. Minimum2 - Retrieve the minimum value in the interval including the Simple Bounding Values.
13. Maximum2 - Retrieve the maximum value in the interval including the Simple Bounding Values.
14. MinimumActualTime2 - Retrieve the minimum value with the actual timestamp including the Simple Bounding Values.
15. MaximumActualTime2 - Retrieve the maximum value with the actual timestamp including the Simple Bounding Values.
16. Range2 - Retrieve the difference between the Minimum2 and Maximum2 value over the interval.
17. Count - Retrieve the number of raw values over the interval.
18. DurationInStateZero - Retrieve the time a Boolean or numeric was in a zero state using Simple Bounding Values.
19. DurationInStateNonZero - Retrieve the time a Boolean or numeric was in a non-zero state using Simple Bounding Values.
20. NumberofTransitions - Retrieve the number of changes between zero and non-zero that a Boolean or numeric value experienced in the interval.
21. Start - Retrieve the first value in the interval.
22. End - Retrieve the last value in the interval.
23. Delta - Retrieve the difference between the Start and End value in the interval.
24. StartBound - Retrieve the value at the beginning of the interval using Simple Bounding Values.
25. EndBound - Retrieve the value at the end of the interval using Simple Bounding Values.
26. DeltaBounds - Retrieve the difference between the StartBound and EndBound value in the interval.
27. Instant - Retrieve the value at the exact beginning of the interval.
28. DurationGood - Retrieve the total duration of time in the interval during which the data is Good.
29. DurationBad - Retrieve the total duration of time in the interval during which the data is Bad.
30. PercentGood - Retrieve the percentage of data (0 to 100) in the interval which has Good StatusCode
31. PercentBad - Retrieve the percentage of data (0 to 100) in the interval which has Bad StatusCode.
32. WorstQuality - Retrieve the worst StatusCode of data in the interval.
33. WorstQuality2 - Retrieve the worst StatusCode of data in the interval including the Simple Bounding Values.
34. StandardDeviationSample - Retrieve the standard deviation for the interval for a sample of the population (n-1).
35. VarianceSample - Retrieve the variance for the interval as calculated by the StandardDeviationSample.
36. StandardDeviationPopulation - Retrieve the standard deviation for the interval for a complete population (n) which includes Simple Bounding Values.
37. VariancePopulation - Retrieve the variance for the interval as calculated by the StandardDeviationPopulation which includes Simple Bounding Values.

Note that the OPC Foundation updated the way it handles bounding, that is, the value at the beginning and end of the time interval. Previous definitions, like TimeAverage or Maximum, used Interpolated Bounding. Interpolated Bounding only used a value as a start or end bound if the value quality was Good. If it was not, each aggregate would handle the bounding based on additional specifications. Simple Bounding was introduced and new aggregates were created, each marked with a "2", TimeAverage2 or Maximum2. With Simple Bounding, the beginning and end values are used regardless of their quality score. Either aggregate will return the same value if both the bounds are of Good quality, but they will return different values if there is a non-Good quality value that is part of the bound.