TM1 Forum

This is coming up quite a bit at the moment, so I wanted to put together a best practices type post to be referenced. Please add in your methods. If someone has time, a Blueprint model illustrating a couple of approaches would be very useful.

The issue
TM1 easily consolidates values up, but it is not native to provide an average at a consolidated level.

There are two very common examples of this which can be used illustrate solutions. Firstly in a sales cube, the Price element should not be consolidated, but rather a weighted average of sold goods. Secondly, in a forex cube, it may be useful to have the quarterly or annual exchange rate as an average of the periods within the quarter/year.

Method 1: Back calculating, weighted average
TM1 allows you to have separate rules for N level (leaf) elements and C level (consolidated) elements. This can be useful to back calculate an average such as 'price'. In the above example, the Value of sales is calculated at the lowest (N) level, then allowed to consolidate up. Quantity is input and then allowed to consolidate up. At the lowest level, Price can be input. The average Price at the consolidated level can then be found by dividing the consolidated Value of Sales by the consolidated Quantity.

Method 2: Extra elements, simple average
This method works well for the forex example. In the measures dimension have three elements, Input, Counter and Average and enter any values into the Input element. Then have the following rule: This method will count up the number of entries, then still consolidate the Input elements as normal. It will then divide the consolidated Input by the number of entries as counted by Counter to give a simple Average.

The Average at the N level will be the same as the Input, as Input/1=Input and the rule has been set to apply to both N and C levels. Consequently Average will always make sense at all levels, whereas Input will only make sense at the N level.

Method 3: Consolidations, simple average
If you wish to average along a single dimension (e.g. Months) you can average the data by changing the weighting factor of the consolidated elements. For example, to average the 12 months rolling into All Months, give each of the months a weighting of 0.0833333. You may wish to have two consolidations - All Months Average (which applies this method), and All Months, which sums up with a weighting of 1.

Possibly worth mentioning too that using the C: level calc for price as discussed you can spread onto the number (e.g. add 10%) and data spreading will pro rate the change.

I had to fight long and hard to get this changed as it didn't work originally and even Manny told me it was illogical until I cornered him at a user event and beat him over the head with examples of why it was in fact quite logical.

I wish I'd had the same sucess with "Undo Spread" - Oh there I go again

We do however support an undo post!!!

Martin, as a minor tweak, I suggest changing

['Counter']=N: if(['Input'] <> 0, 1, 0); (if there's a value in 'Input' then put a counter in, otherwise don't)
['Average'] = ['Input'] \ ['Counter']; (the average is the sum of 'Input' divided by the sum of 'Counter')

to
['Counter']=N: sign(['Input']; (if there's a value in 'Input' then put a counter in, otherwise don't)
['Average'] = ['Input'] \ ['Counter']; (the average is the sum of 'Input' divided by the sum of 'Counter')

or even just

['Average'] = ['Input'] \ sign(['Input''];

As you know I feel that keeping the if count down helps with readability - and Sign is a useful function for this.

Just to be precise, instead of sign(['Input']) one should put abs(sign(['Input'])), as sign from negative value equals -1.

There is also the "ConsolidateChildren" rule function, which allows you to arrive at a consolidated value across a time or product or whatever dimension that maybe. This is useful when wanting to create a weighted average.

Not quite sure how that would help more than a normal consolidation, but in any case just wanted to point out the ConsolidateChildren is very expensive in terms of calculation time. I've only tried to use it once and found it blew out the RAM on my cube and made it run much slower.

Martin

I've refined my approach somewhat over the 5 years (5 years!!!!) since I wrote this post and have a fourth method that's a hybird of method 1 and 2. It's a bit more complex, but it allows for handling multiple averages in a cube fairly cleanly.

Method 4: Numerator/Denominator
This method allows flexibility over whether you use a simple or weighted average, or something a little more odd ball. A new dimension is required in the cube, that I usually call "Average", which three elements, Input, Numerator, Denominator. I sometimes hide Numerator/Denominator from the users via security to prevent them getting confused.
I've attached a very basic example model that shows this approach.