Rules Question on Calculation by Level

[cberg]

Is there a way to restrict a calculation to two specific levels using a rule.

Right now I have a formula to determine what percent of people want to buy different flavors of ice cream

['Purchase%_Total']=C:['IcecreamSold_Units']\['IcecreamAvail_Units'];

I want to restrict the C: calculation to levels 4 and 3 of my [SKU] dimension and force the result of 3 into levels 2 and 1 and 0.

Is this possible everything I have read seems to indicate that it's either all Consolidated Levels, Just the N level or Everything.


CBerg

[declanr]

You can use an if statement and ELLEV function on the right hand side.

[tomok]

Read up on the rules function ELLEV.

[cberg]

Great thanks I can isolate the levels


['Purchase%_Total']=IF(ELLEV('d.sku',!d.SKU)>2,['IcecreamSold_Units']\['IcecreamAvail_Units'],[ELLEV('d.sku',!d.SKU)=3,'IcecreamSold_Units']\ELLEV[d.sku',!d.SKU)=3'IcecreamAvail_Units']));


What I am struggling with is how to push the level 3 results into levels 2 through 0.

Chris

[tomok]

What I am struggling with is how to push the level 3 results into levels 2 through 0.

Here's the issue in a nutshell: In TM1 Rules, just like formulas in Excel, you don't write formulas to push something down, you write formulas to pull something in. For example, if you had an empty cell in Excel, let's say C3, and you wanted to pull into it the value from A1 (the same thing as pushing the value from A1 to C3) you wouldn't write the formula in A1 and say "push this down to C3"? No, you would write the formula in C3 and say "pull in from A1".

Your example is the same. Instead of pushing down you need to pull in. For levels 0, 1, and 2, you need to write your formula to pull in from their respective parents, working your way up the chain to levels 3 and 4. Something like this:

Code: Select all

['Purchase%_Total']=IF(ELLEV('d.sku',!d.SKU) < 3, DB(CubeName, !Dim1, !Dim2, !Dimn, !d.SKU, ELPAR('d.SKU, !d.SKU,1), 'Purchase%_Total');

This is not the exact code because I don't know the dimensionality of your cube and I don't know how many trees you have in the d.SKU dimension but this should give you an idea as to how it would be done.