Averages for Consolidations

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Martin Ryan
Site Admin
Posts: 1954
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 » Fri Oct 24, 2008 11:01 am

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: 329
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007
Location: Lytham UK
Contact:

Re: Averages for Consolidations

Post by John Hobson » Fri Oct 24, 2008 12:29 pm

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

TM1 10.2.0 / Win7 / XL 2010

User avatar
jim wood
Site Admin
Posts: 3617
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Averages for Consolidations

Post by jim wood » Fri Oct 24, 2008 1:42 pm

We do however support an undo post!!! :twisted:
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

David Usherwood
Site Admin
Posts: 1337
Joined: Wed May 28, 2008 9:09 am

Re: Averages for Consolidations

Post by David Usherwood » Sat Oct 25, 2008 11:14 am

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 » Thu Aug 27, 2009 10:23 pm

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 » Fri Oct 16, 2009 5:27 am

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: 1954
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 » Fri Oct 16, 2009 6:41 am

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: 1954
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 » Wed Aug 28, 2013 4:25 am

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 1027 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