Aggregation Type is a parameter that is set while creating a KPI/Measure. It determines what happens to data when viewed using different calendars.
When using the view calendar to look at your data, using a calendar period longer than the data collection calendar selected to collect data, for example using a Fiscal Year calendar to look at data that is collected monthly, the results are calculated based on Aggregation Type.
Aggregation type has four possible values:
- Sum – Values added monthly will be summed up when viewed quarterly or yearly e.g. Revenue is usually defined as sum.
- Average – Values added monthly will be averaged when viewed quarterly or yearly – e.g. % Customer Satisfaction is usually averaged.
- Geometric Mean – This is a special aggregation often used by statisticians to deal with large variances, see below for more details.
- Last Value – For values added monthly, the last value added will be used when viewed quarterly or yearly – e.g. Cumulative Sales is usually set to last value.
The geometric mean is a special way of calculating the average of multiple numbers used in various statistical models. The technical definition of geometric mean is: The nth root of a product of n numbers.
In practice, the mathematics is fairly simple. With the “average” aggregation type, three numbers are aggregated using (a + b + c) ⁄ 3. For geometric mean, the equation is √ a ∗ b ∗ c .
A geometric Mean is often used to deal with cases where there may be ’outliers’ that skew the data artificially.
For example; let’s say you want to know the “Average number of miles travelled per day” by a truck driver that usually delivers locally. Typically you would record the number of miles per day over a period of one month and divide by the number of ‘driver days’ to give an average.