Averages for Consolidations

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Averages for Consolidations

Post by Martin Ryan »

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'.

Code: Select all

['Price'] = N: stet; (allow input at the lowest level)
['Qty'] = N: stet; (input at lowest level)
['Value of Sales'] = N: ['Price'] * ['Qty']; (the total value of sales for a unit is price * quantity)
['Price'] = C: ['Value of Sales'] \ ['Qty']; (the average price is the total value of sales divided by the total quanity
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:

Code: Select all

['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')
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.
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

Re: Averages for Consolidations

Post by John Hobson »

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 :mrgreen:
John Hobson
The Planning Factory
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Averages for Consolidations

Post by jim wood »

We do however support an undo post!!! :twisted:
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Averages for Consolidations

Post by David Usherwood »

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.
jstrygner
MVP
Posts: 195
Joined: Wed Jul 22, 2009 10:35 pm
OLAP Product: TM1
Version: 9.5.2 FP3
Excel Version: 2010

Re: Averages for Consolidations

Post by jstrygner »

Just to be precise, instead of sign(['Input']) one should put abs(sign(['Input'])), as sign from negative value equals -1.
ssp
Posts: 24
Joined: Sun Jul 13, 2008 12:31 am

Re: Averages for Consolidations

Post by ssp »

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.
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Averages for Consolidations

Post by Martin Ryan »

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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Averages for Consolidations

Post by Martin Ryan »

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.

Code: Select all

SkipCheck;
# At a consolidated level Input is calculated, so reporting can always be done off the "Input" element regardless of what level in the hierarchy you're at
# Specify which measures should be calculated.  Other measures will sum up like a normal consolidation instead of calculating an average
['Input', {'FTE', 'Price', 'FX Rate'}] = C: ['Numerator'] \ ['Denominator'];
# Usually the numerator will be the number you're averaging.  E.g. FTE or FX rates, but for weighted averages may be something else
['Numerator', 'Price'] = N: ['Input', 'Value of Sales'];
# Catch all, including FTE and FX Rate measure
['Numerator'] = N: ['Input'];
# Denominator will generally either be 1 for a simple average or some other measure for a weighted average
['Denominator', 'Price'] = N: ['Input', 'Qty'];
# Catch all, including FTE measure
['Denominator'] = N: if(['Input']<>0, 1, 0);

# Other calcs not directly related to the average
['Value of sales'] = N: ['Price'] * ['Qty'];

Feeders;
['Input'] => ['Numerator'], ['Denominator'];
['Qty'] => ['Value of sales'];
I've attached a very basic example model that shows this approach.
Attachments
Data.zip
(21.64 KiB) Downloaded 1543 times
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Post Reply