Aggregation Modes

Unknown macro: {scrollbar}

When working with basic measures, it is necesary to define the aggregation mode used for the measure. Derived measures do not need this setting since these measures calculate their values for each level in the hierarchy.

Aggregation modes include:

Mode	Description
Sum	The sum of all values of the level immediately below
Max	The maximum value of the level immediately below
Min	The minimum value of the level immediately below
Average	The average of all values of the hierarchy leaves. The calculation is based on every row in the original datasource and not directly on the tuples in the cube.
Weighted Average	The weighted average, weighted by the field indicated. For instance, the average sale price weighted by quantity would be: (Price * Quantity) / Quantity
Count	The number of rows that match the query filters. All rows in the original datasource are considered, not the actual tuples in the cube
Elapsed Average	Corresponds to a special aggregation involving time. For insntace, it is a good aggregation mode to know the average duration time for tasks.
Standard Deviation	This kind of aggretation mode is described in the following [tutorial]

Example

Consider a hierarchy level with three elements, E1, E2 and E3 and the superior level with the element ET.

Consider also that the leaves are built from 5 rows in the datasources.

The following is the expression used in each aggregation mode when calculation the t value of the ET element:

Mode	Expression
Sum	t = (a + b + c)
Max	t = Max(a, b, c)
Min	t = Min(a, b, c)
Average	t = ((a + b + c) / 5)
Count	t = 5
Weighted Average	If the field used for weighing has P1, P2 and P3 values for each E1, E2 and E3 element respectively:t = ((aP1 + bP2 + c*P3) / (P1 + P2 + P3))

Special aggretation modes for Dates

It is possible to define the following special aggregation for Dates:

Last	Takes the value in the level inmediately below corresponding to the last element in the period.
Dynamic Last	Takes the value in the level inmediately below corresponding to the last element with data in the period. The difference with the previous option is that it adjusts itself to display always the last element with data.
First	Takes the value in the level inmediately below corresponding to the first element in the period.
Dynamic First	Takes the value in the level inmediately below corresponding to the first element with data in the period. The difference with the previous option is that it adjusts itself to display always the first element with data.
Average	Takes the value corresponding to the element according to its standard aggregation mode and divides it between the number of leaves in the Date dimension under the scope of the query.
Children Average	Takes the value corresponding to the element according to its standard aggregation mode and divides it between the number of children in the Date dimension under the scope of the query.
First with Data	See [Due Balance Aggregation]
Last with Data	See [Due Balance Aggregation]

Suppose that there is a date dimension with three levels: Year, Quarter, Month.

Figure on the left shows an example for year 2003 where V1 to V4 represent data from the datasources for an Inventory measure.

Values for the higher levels in the hierarchy are calculated based on the aggregation mode chosen.

The following table shows examples for the measure using the different aggregation modes, along with an explantation of each case.

Aggregation Type	Values	Explanation
First	Q1 = NaN Q2 = Apr (v3) 2003 = Q1 = NaN	Since there is no data for Jan, which would be the first element in Q1, the value for Q1 is set to NaN. Then the value for 2003 equals that for Q1 since it is the first element in the year. Q2 equals Apr because it is the last element in the period.
Last	Q1 = Mar (v2) Q2 = Jun (NaN) 2003 = NaN	Q1 equals to Mar (v2), because March is the last element in Q1. Q2 equals to Jun. Since there is no data for June Q2 is set to NaN. Finally 2003 is set to NaN as well because there is no data for Q4.
Dynamic First	Q1 = Feb (v1) Q2 = Apr (v3) 2003 = Q1	Q1 equals to Feb (v1) because February is the first element in Q1 with data. Q2 equals to Apr (v3) beause April is the first element in Q2 with data. Finally 2003 equals to Q1 because it is the first element in the year and has data.
Dynamic Last	Q1 = Mar (v2) Q2 = May (v4) 2003 = Q2	Q1 equals to Mar (v2) because March is the last element in Q1 with data. Q2 equals to May (v4) because May is the las element in Q2 with data. Finally 2003 equals to Q2 because it is the last element in the year with data. (Q4 has no data).
Average	Q1 = (v1 + v2) / 2 Q2 = (v3 + v4) / 2 2003 = (v1 + v2 + v3 + v4) / 4	Value for Q1 is the sum of the values of its elements, divided by the number of leaves. Value for Q2 is similar to Q1 with its corresponding data. Finally 2003 is the sum of values divided by the 4 leaves
Children Average	Q1 = (v1 + v2) / 2 Q2 = (v3 + v4) / 2 2003 = (v1 + v2 + v3 + v4) / 2	Similar to the previous case, only that the year is divided by 2 (the two quarters children of the year)

Note
Averages take into account elements created while building the cube that contain data other than zero.