TM1 Forum

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

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

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

Thanks. I'll give those a try and get some more info when I get into the office.

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

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

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

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

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.

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