Ratio rules

[SimonLAylett]

Hi guys,

I'm trying to perfect ratios on a basic P&L cube and do different things at different levels, and i'm having some trouble.

I'm calculating a simple ratio on a cube as follows;

['Gross Commission/Income']=['Commissions']\['Income'];

Where ['Gross Commission/Income'] is a ratio nominal in the dimension 'nominal', and Commissions & Income are value nominals in the same dimension.

I want to be able to use the above calculation for the 'Actual' and 'Budget' Ledger types in the dimension 'Version', but for 'ActvsBud', which is a consolidation of the other 2, I want the consolidation to work and do ['Actual']-['Budget'].

I'm sure this is as easy a query as they come, but it's stumping me. N: level/ C: level rules are swirling round my head, so is 'IF(ELLISANC)' but i can't fathom it.

Any help greatly appreciated

Cheers,

Simon

[SimonLAylett]

I should add, SKIPCHECK; is turned on and I'm not feeding anything currently (Although advice on the latter is welcome!)

Thanks,

Simon

[asutcliffe]

I'm calculating a simple ratio on a cube as follows;

['Gross Commission/Income']=['Commissions']\['Income'];

Where ['Gross Commission/Income'] is a ratio nominal in the dimension 'nominal', and Commissions & Income are value nominals in the same dimension.

I want to be able to use the above calculation for the 'Actual' and 'Budget' Ledger types in the dimension 'Version', but for 'ActvsBud', which is a consolidation of the other 2, I want the consolidation to work and do ['Actual']-['Budget'].

I'm sure this is as easy a query as they come, but it's stumping me. N: level/ C: level rules are swirling round my head, so is 'IF(ELLISANC)' but i can't fathom it.

Are 'Actual' and 'Budget' leaf elements or consolidations? Is 'Gross Commission/Income' a leaf element or consolidation?

If they're all leaf level, I think you want something like this:

Code: Select all

[{'Actual', 'Budget'}, 'Gross Commission/Income']= ['Commissions']\['Income'];

Omitting N: or C: means it will be applied to both but since you've specified {'Actual', 'Budget'} on the left hand side, it won't apply to 'ActvsBud'.

If 'Actual' and 'Budget' are consolidations you want the rule to apply to elements under them, then you might try something like this:

Code: Select all

['Gross Commission/Income']= If(ElisAnc('Version', 'Actual', !Version)  = 1 %  ElisAnc('Version', 'Budget', !Version)  = 1,
['Commissions']\['Income'],
Continue);

Does that help at all?

[Steve Rowe]

You might want to check rule order too, the first rule that applies to a cellwins.

[ 'ActvsBud' , 'Gross Commission/Income']=['Actual']-['Budget'];
['Gross Commission/Income']=['Commissions']\['Income'];

Should work
btw if your variance calc is as simple as stated and you don't have mess around with the sign because of the sign in the underlying data then you should be doing ActvsBud in a consolidation since it's much more efficent.

Cheers,