Consolidated Rules

[erakeman]

Hi everyone. Full transparency, I am an FP&A guy with limited rule/feeder writing experience.
I am trying to write a rule to calculate % achievement of plan (and growth over prior year). Simple calculation (Actual \ Budget). I have the rule working fine for N level data, but figures are consolidating when I look at Total Company. How can I write the rule such that the consolidated values are not the sum of the children but rather rule generated calculations? I tried changing the N: to C: but no luck. Thought I would throw it out to the smart folks before I spin my wheels for hours.
Thanks!

SkipCheck;
[VersionMgmt:'Actual vs SalesPlan %']=N:
## only do the calculation for current budget period
If(Attrs('TimeMgmt', !TimeMgmt, 'BudFcstVar')@='Y',
([VersionMgmt:'Actual']\[VersionMgmt:'SalesPlan'])
,stet);

Feeders;
[VersionMgmt:'Actual']=>[VersionMgmt:'Actual vs SalesPlan %'];
[VersionMgmt:'SalesPlan']=>[VersionMgmt:'Actual vs SalesPlan %'];

[Wim Gielis]

Hi,

Try:

Code: Select all

SkipCheck;
## only do the calculation for current budget period
['Actual vs SalesPlan %']=N:If(Attrs('TimeMgmt', !TimeMgmt, 'BudFcstVar')@='Y',['Actual']\['SalesPlan'],stet);C:['Actual']\['SalesPlan'];

Feeders;
[{'Actual','SalesPlan'}]=>['Actual vs SalesPlan %'];

Also notice that I took out the dimension names. You can indeed include them, but with lots of rules this might be too much text.
Make sure rules-calculated elements are unique over dimensions in your cube, if not you can still add the dimension names. But when you do so, do not forget the single quotes around the dimension name !

Wim

[David Usherwood]

If you want to do the calculation at c and n levels, just remove the n:

Code: Select all

SkipCheck;
['Actual vs SalesPlan %']=
## only do the calculation for current budget period
If(Attrs('TimeMgmt', !TimeMgmt, 'BudFcstVar')@='Y',
(['Actual']\['SalesPlan'])
,stet);

Feeders;
['SalesPlan']=>['Actual vs SalesPlan %'];

I'm glad you are using n: as lots of newbies miss it. But this is a case where you shouldn't. I describe this type of rule as KPI rules - you are dividing a line by another line and you want it all the way up the hierarchies.
Also - you don't need to feed from both actual and salesplan. One is sufficient for a divide (or multiply) rule.

[lotsaram]

Also - you don't need to feed from both actual and salesplan. One is sufficient for a divide (or multiply) rule.

... well actually you may not need to feed at all unless there is a requirement for the KPI measure to display in a null suppressed view.

[erakeman]

If you want to do the calculation at c and n levels, just remove the n:

What is the upside / downside to just removing the n: as opposed to adding a c: ?

[lotsaram]

If you want to do the calculation at c and n levels, just remove the n:

What is the upside / downside to just removing the n: as opposed to adding a c: ?

If you want to apply the same rule to both leaf and consolidated elements then simply don't use a qualifier. Use separate N: and C: qualifiers if you want to apply a different calculation depending on whether the cell is simple or consolidated.