TM1 Forum

A general question here on how to control when a rule consolidates versus calculates across dimensions.

Take for example you have a cube with the following two dimensions: product and period

The product dimension has a couple levels:

Total Products
Product Group 1
Product 1
Product 2
...

The period dimension has a couple levels:

Total Months
Jan
Feb
Mar
Jan YTD
Jan
Feb YTD
Jan
Feb
Mar YTD
Jan
Feb
Mar
...

When at a consolidated level for the period dimension the rules should be applied but when at a consolidated level for products it should be the rollup of the leaf elements.

I tried something like [measure]=IF(ELLEV('Products'!Product)>0,STET,CONTINUE); while this get me a much closer number, it is still not accurate. Any thoughts?

You're pretty close, you just need to use the 'or' (%) or 'and' (&) operators or alternatively a nested if statement to deal with the ellev of the product because of course it's possible that the intersection has consolidations for both of the dimensions you mention, so you need to deal with that scenario.

Martin

so basically how do i deal with both being consolidated at the same time? I want the period to perform a calculation and the product to just consolidate its childern. (ConsolidateChildern('Product') always returns a missing colon error)

This is what i assume you mean:
[measure]=if(ellev('Product',!Product)>0 & ellev('Period',!Period)>0,STET,Continue);

Was "ConsolidateChildern" a typo in TM1 or in your post? It should be ConsolidateChildren. But this function is dog slow and should be avoided wherever possible.

I'd have thought you want something like this:
['measure'] = if( ellev('Product', !Product)>0, stet, if(ellev('Period', !Period) > 0, SomeSortOfRule or continue, stet));

Can you give a bit more detail on what you're trying to achieve, including what the measure is? Are you just trying to work out an average?

It was actually same elements across dimensions and spaces in my dimension name causing the ConsolidateChildren not to work. Added the 'dimension name':'element' and that started working.

I added C: and N: rules to accomplish what I was looking for.

['variance']=C:IF(ELLEV('Period',!Period)>0, [unit chg]*[price], STET);
N:[unit chg]*[price];

Basically my price measure is calculated and doesn't make sense across products nor would the variance measure. Is there a better way to do this or is this what you would recommend?

I'm not entirely sure what "unit chg" might mean, but a rule along the lines of this one may be applicable

# Price drives volume at the lowest level
['Revenue'] = N: ['Price'] * ['Volume'];
# Revenue drives price at the higher level, gives a weighted average
['Price'] = C: ['Revenue'] \ ['Volume']';

There are some other ideas on averages here: http://www.tm1forum.com/viewtopic.php?f=21&t=388&p=2370