Aggregation

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.

Country Year Revenue
DE 2018 100
US 2018 200
DE 2019 150
US 2019 250

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:

Year Sum Average Min Max
2018 300 150 100 200
2019 400 200 150 250

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. 

Country Quarter Year Revenue
DE Q1 2018 15
DE Q2 2018 15
DE Q3 2018 20
DE Q4 2018 50
US Q1 2018 30
US Q2 2018 30
US Q3 2018 40
US Q4 2018 100

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.

Year ClosingSUM ClosingAVG
2018 150 75



Was this helpful?

Yes | Somewhat | No