ConsolidatedAvg is used to calculate a single consolidated average value of child values at the n level. For example, if you have three child elements with 10, 15 and 11 in a measure, the consolidated average would be (10+15+11)/3, so 12.
Syntax of ConsolidatedAvg
In a TM1 Rule, the syntax is:
['Measure'] = C: ConsolidatedAvg(flag-value, '', Element1, Element2, Elementn, 'Measure');
And in a Turbo Integrator process, the syntax is similar:
nAverage = ConsolidatedAvg(flag-value, 'CubeName', Element1, Element2, Elementn, 'Measure');
Where:
- flag-value is either a 0, 1, 2 or 3.
- 0 use all values
- 1 use the consolidation weighting (so the -1, 0, or 1 in weighting)
- 2 ignore zero values
- 3 combines the above two (in other words, 1 and 2 are not mutually exclusive)
- CubeName is the name of the cube you are working with. This is not required for the rule version if your rule is addressing the current cube.
- Element1, Element2, Elementn are elements or aliases from the dimensions of the cube. This should be in the exact order of the dimensions in the cube (so like they are in a DB). Note that for ConsolidatedAvg you don’t have to use a specific element in each position here. You can use an !DimName in place of a specific element.
- ‘Measure’ is an element from the measures dimension (yes, I know this is superfluous, but your measures dimension should be the last one in the list!)
Note that when using ConsolidatedAvg it takes the average of the n level elements, and not the immediate children where there are multiple levels in a hierarchy.
Further Notes from IBM
IBM has provided us with the following notes. They explain some quicks about the calculation of averages in TM1 that you should be aware of:
- Suppose zero is specified as the weighting of some consolidated elements. In that case, the Tm1s.cfg configuration parameter ZeroWeightOptimization=F must be set for these elements to be included in the calculation of the average value in a consolidation. Without this configuration parameter, the elements for which the weighting is zero are eliminated from the consolidation list and are not included when calculating the average value in a consolidation.
- If you want cells containing the value zero to be included when calculating the average, UNDEFVALS must be set in the rules for the cube that is specified by the cube-name argument. This ensures that when a zero is assigned to a cell of the cube, an actual zero value is stored in the cell and the zero value is included when calculating the average value in a consolidation.
- If the rules for the cube that is specified by the cube-name argument include a SKIPCHECK statement, the average value in consolidation will ignore zeros. Remove the SKIPCHECK statement from the rule to include zeros in the consolidation average.
Example of ConsolidatedAvg
Here, our example is to calculate the average price from the Price measure in the Subscription cube. We want to ignore zero values.
['Average Price'] = ConsolidatedAvg(2,'Subscription',!Year,!Month,!Scenario,!Department,!Product,'Price');
So if in our cube we had the following values:
Product 1 | 10 |
Product 2 | 12 |
Product 3 | 14 |
Product 4 | 0 |
Product 5 | 0 |
Product 6 | 22 |
From these, the average calculation would sum those and divide by 4 (excluding the two zeros), so calculating the average as 15.
Usage
You can use ConsolidatedAvg in both Rules and TI Processes.