Aggregation is the process of reducing a data set's complexity by summarizing multiple values. This typically happens when viewing a data set with multiple dimensions in a chart that displays the data using only a subset of the available dimensions. For example, imagine this data set for Revenue by country and year.
When requesting the data only by year and not by country, we need to summarize the two values for 2018 and 2019 into a single value accordingly. The mechanism for this summarization is described using an aggregation function which can be any of the following:
- Sum: the sum of all values
- Average: the average of the values
- Min: takes the smallest value and ignore the rest
- Max: takes the largest value and ignore the rest
- ClosingSUM: takes the last value of a period if aggregating a time dimension, if another dimension is aggregated the sum is calculated
- ClosingAVG: takes the last value of a period if aggregating a time dimension, if another dimension is aggregated the sum is calculated
So for the example above the aggregation using the different functions would result in:
In the model editor, you can define an aggregation function per node via the inspector. This function will always be used to aggregate the values of this node as part of any calculation. If you define an explicit aggregation using ROLLUP this can override the default aggregation function of a node.
ClosingSUM and ClosingAVG
The aggregation functions ClosingSUM and ClosingAVG are like the traditional SUM and AVG with the exception of how the time dimension is aggregated. To outline the mechanics, let's consider the example from above for 2018 with quarters.
When aggregating the data on year using ClosingSUM or ClosingAVG, the aggregation will take the closing value of the period (which is Q4) and calculate the SUM or AVG over all countries.