The aggregation setting of a node defines how the data is summarized when shown with less than its "full dimensionality". This setting is very visible when looking at the data in a tree view, as there the data is often shown on a per-year level. It also affects how the data is "Rolled up", if the ROLLUP or DROPLEVEL function is used.
Among the standard options you will find:
- AVG (See weighted average below)
|SUM (default)||Sum of all values|
|AVG||The average value. To calculate weighted averages, see below|
|MIN / MAX||The minimal value (on that specific aggregation level), e.g. can be a min-value per year & product|
|CLOSINGSUM||The sum of the values at the end of each time value (e.g. Stock level on a node that contains in/out to warehouse)|
|CLOSINGAVG||The avg value within a time period, e.g. average cash balance on a node that contains the balance.|
|NONE||The node will not be shown aggregated.|
NEW IN VERSION 2.1
Weighted Average by
|You can specify a node which is used for weighting, e.g. use volume to weight the price to get an average price of products sold. See details below.|
The standard aggregation types only use the data of the node itself. However, typical drivers are cost ratios, prices, etc that must be weighted by a volume to make sense on an aggregated level. The weighted average aggregation setting allows you to define such a node. When using the weighted average, keep the following in mind:
- The weighting node needs to have at least the levels of the node.
- The weighted average is not supported for nodes using the RATIO function.
- The average is calculated as 'Node' * 'Weight' / 'Weight'. When using the node in a chart, the aggregation to the requested chart dimensionality happens individually for the numerator ( 'Node' * 'Weight') and the denominator ('Weight') before the division and always using SUM as aggregation method (independent of the aggregation settings of 'Node' and 'Weight').