Page Comparison

{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|O3PS:Statistical Functions] |

h3. 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 = ((a*P1 + b*P2 + c*P3) / (P1 + P2 + P3)) |

h3. 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|O3PS:Due Balance Aggregation] ||
|| Last with Data | See [Due Balance Aggregation|O3PS:Due Balance Aggregation] ||
!O3Designer-Measures-SpecialDateAggregationSample.png|align=left, vspace=10, hspace=10!

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 | {code}