Exploring TM1 - a Chartertech Company
Search
Close this search box.

ConsolidatedAvg TM1 Function: How to Use, Example, and Syntax

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 110
Product 212
Product 314
Product 40
Product 50
Product 622

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.

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

John Vaughan

John Vaughan

John is a CPA, MBA and has been a Performance Management consultant for over 25 years. He is the founder of ExploringTM1 and highly regarded for his experience combining financial management with corporate planning, reporting and analysis. He lives in Sydney with his wife, two of his three children, their cat, Freckles, a bunch of chooks and some fish. John is a sports nut, who played rugby until he was 40, started playing football at 54 and loves being outdoors.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In