Getting an average instead of Consolidation

Post Reply
jshurak
Posts: 9
Joined: Thu Aug 28, 2008 7:59 pm

Getting an average instead of Consolidation

Post by jshurak »

Hi everyone,

I've got a consolidation that I need to be an average. I should say, we are pulling an average in a SQL statement from our source and TM1 is automatically summing it up. I need to be able to maintain the original figure pulled. I'm new to this and had a day long Rules training class, but haven't touched them in over a month. Thanks for your help.

Jeff
pobrouwers
Posts: 34
Joined: Mon Aug 11, 2008 7:37 pm
OLAP Product: IBM Cognos TM1
Version: 9.5.2
Excel Version: 2003-2007
Location: France

Re: Getting an average instead of Consolidation

Post by pobrouwers »

Hello Jeff,

This a example :

['Cost']=C:['Sales']\['Volume'];
['Sales']=N:['Volume']*['Cost'];

Y'll get an average for cost...

You can also write this if you have a source for the elements N (N is the lower element in a dimension):
['Cost']=N:DB('price and cost,'Price',!2 Products,!5 Version);C:['Sales']\['Volume'];
['Sales']=N:['Volume']*['Cost'];

I hope that it 'll hep you...
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: Getting an average instead of Consolidation

Post by Martin Ryan »

Getting averages instead of summing things up is always an interesting problem in TM1. The post above is a good one if that's what you're trying to do, but if it doesn't fit in exactly with what you're trying to achieve please post a bit more detail about the numbers you're inputting and what you want to get out and we can try to give more specific advice.

Cheers,
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
jshurak
Posts: 9
Joined: Thu Aug 28, 2008 7:59 pm

Re: Getting an average instead of Consolidation

Post by jshurak »

Thanks. I'll give those a try and get some more info when I get into the office.
crash0verride
Posts: 5
Joined: Mon Oct 20, 2008 10:03 am
Version: TM1 9.0.1.181 SP3
Excel Version: MS Excel 2003 SP3
Location: Manila, Philippines

Re: Getting an average instead of Consolidation

Post by crash0verride »

I also having the same problem of getting an average instead of consolidation :(

Here's what suppose to happen.

The consolidated element (C) must get its sum value and divide it by the number of components it has
like ['General Average']=sum of child values / total number of childs.

I already tried it on rules but i just can't get it correctly :(
ScottW
Regular Participant
Posts: 152
Joined: Fri May 23, 2008 12:08 am
OLAP Product: TM1 CX
Version: 9.5 9.4.1 9.1.4 9.0 8.4
Excel Version: 2003 2007
Location: Melbourne, Australia
Contact:

Re: Getting an average instead of Consolidation

Post by ScottW »

Overriding TM1's natural consolidation where averages are required is a classic problem. It is conveniently sidestepped in most training material and examples by giving the example already mentioned

Code: Select all

['Sales'] = N: ['Volume'] * ['Cost'];
['Cost'] = C: ['Sales'] \ ['Volume'];
However this conveniently ignores many real world data examples where you may only be loading Sales to leaf cells (as opposed to price & volume) but need to see the average of Sales not its sum at consolidated nodes. This can be achieved quite easily with TM1 rules but generally not without using additional measures. an example would be:

Code: Select all

['Sales Counter'] = N: If(['Sales'] <> 0, 1, 0);
['Sales Ghost'] = N: ['Sales'];
['Sales'] = C: ['Sales Ghost'] \ ['Sales Counter'];

Feeders;
['Sales'] => ['Sales Counter'], ['Sales Ghost'];
The reason for the "ghost" as well as the counter is that this prevents a circular reference in the calculation. TM1 is a great tool but I'll readily admit that the calculation of averages is not as intuitive or straightforward as it could/should be (especially for novices).

I'm pretty sure the example above should solve crash0veride's problem as described. Not sure if more detail is needed from jshurak, if it's a weighted rather than simple average then it's possible but a little more complicated.
Cheers,
Scott W
Cubewise
www.cubewise.com
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Getting an average instead of Consolidation

Post by paulsimon »

Jeff

The previous solutions are the best approach if you can get the underlying data that was used to calculate the average from the source system. However, sometimes all you can get is the average. If this is the case then a rule like the following will help:

Code: Select all

skipcheck ;

['Ave Thing'] = 
  IF( ellev('Trans_Mth', !Trans_Mth) = 0 
       ,
       CONTINUE
       ,
       DB('HO_Costs_for_Alloc',
             !Scenario,
             elcomp('Trans_Mth',!Trans_Mth,elcompn('Trans_Mth',!Trans_Mth) ),
             !HO_Costs_for_Alloc_Meas
      )
  ) ;
This rule says that if you are at anything other than the base level in the time dimension, then instead of allowing normal consolidation, then take the value from the last element that consolidates in to the consolidated time element. So for Q1 it takes March, for the Half Year 1 it takes Q2, which will in turn have taken June.

The same sort of approach works with balance data.

Whether taking the value from the last child period is the right thing to do, depends on your particular business situation, but it gives a better answer than consolidating averages.

However, by far the best approach is to get the value and the count and to re-calculate the average in TM1. TM1 can quite happily do this at any level. You don't even need to feed an Average unless you want it to show up in a zero suppressed view.

Regards


Paul Simon
dharav
Regular Participant
Posts: 193
Joined: Wed Apr 02, 2014 6:43 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2010

Re: Getting an average instead of Consolidation

Post by dharav »

Hi Jeff

I have similar problem.

Elements
1 Sides
2 Volume
3 Avg Price

Cube
1Input
2 P&L
3 Trend

Input cube is linked to p&l and p&l is linked to variance cube.

User would enter manually data for forecast period for the Sides and avg price. So we have to calculate the volume=sides*avg price
To calculate avg price at consolidation, I applied the following rule.

[Avg Price]=N:STET;C:(Volume/Sides);
[volume]=N:(sides*avg price);

For Input cube it works as we entering the data.

Problem 1: Now when i saw the same data in to p&l cube than it wasn't there. For all forecast month data was there but for actual month it was not there. All data was there except volume and avg price.

I wrote a rule

[Volume]=db('accounts',!,!,'volume',!);
[Avg Price]=N:DB('Accounts',!,!,'avg price,!),C:(Volume/sides);

After writing this rule data came properly as we wanted in to both actual and forecast month.

How could it possible ? We have established link between input to p&l though this data wasn't transfer properly but when i wrote intra cube rule that it was showing all data correctly placed.

Problem 2:

Now in trend cube, same problem is being there. so tried to write rules same way as i did in P&l cube as stated above though it did not work.

What are the factors or the points i need to consider before applying such avg calculations.?

Is there any other alternative way to do so?

Let me know if further information required on this.

Thanks

Dharav
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Getting an average instead of Consolidation

Post by Steve Rowe »

Suggest you post your feeder statements, rules are only half the story, plus screen shots of where the issue is.
Post your actual code, not retyped, so we can spot errors.
Describe how it is not working in more detail.
If you can see the results at the N level in an un-supressed view then you have a feeder problem.

This kind of rule is hard to feed, strictly impossible with a feeder statement.
[Avg Price]=N:STET;C:(Volume/Sides);

Your C level rule will only calculate correctly if you have input data in the stet area under it, the input data feeds / flags the consolidation for calculation.
Technical Director
www.infocat.co.uk
Dahlia
Posts: 6
Joined: Wed Sep 30, 2015 1:07 am
OLAP Product: TM1
Version: 10-2-2
Excel Version: 2010

Re: Getting an average instead of Consolidation

Post by Dahlia »

Hi Paul,
I created the formula that you suggested and it worked great for a similar scenario. Q2 is June balance, not the sum of April, May, June.
Do you have the related feeder statement?

Thanks,
Dahlia
Post Reply