YTD rule calculation?

[BigG]

Hi All, without blurring a previous post I had http://forums.olapforums.com/viewtopic.php?f=3&t=3900 I have a question around applying a rule calculation to achieve YTD of a Month only timescale for one single measure element out of many in the measure dimension.

I could create a YTD rollup in Month timescale and this may be the most workable solution, but since this is only one instance for the YTD requirement I would like to try my hand with a rule calc.


A suggestion of from mce

calculating YTD using rules. You would simply have a separate dimension (or you can also use your measure dimension) that has "Period Value" and "YTD Value" elements. If you want to achieve this via feeder-less rules, you can make "YTD Value" a parent of "Period Value".

points to the YTD Value" a parent of "Period Value" for measure dimension, but what I cannot work out is the rule that would calculate the YTD. I have a month_number attribute in Month dim which may help? Does anyone have a suggestion on the syntax or methof in the rule?

Thanks in advance

[captnknz]

YTD = N:
DB(Cube,DIMNM('Mth',DIMIX('Mth',!mth)-1),'YTD') + ['Mth'];

1. You can create two attributes; one for the rule (P-1), one for the feeder (P+1)
1a. Meaning the above could be restated as:

YTD = N:
DB(Cube,ATTRS('Mth','P-1),'YTD') + ['Mth'];

Feeder:
Mth => DB(Cube,ATTRS('Mth','P+1'),'YTD');

Hope this helps, let me know if you need more

[BigG]

Hi Thanks for the reply.

I havent had much luck with getting the calc to actually work. I can follow the logic you apply but result is no calculated value. I was a bit unsure about the feeder 'Month' reference too.

I created a YTD measure 'BB Purchase Vol YTD' at level 0
In Month dim We have attributes that display Previous_Month and Next_Month (both Text month descriptions)

Then applied this rule

Code: Select all

['BB Purchase Vol YTD'] = N:
DB('BU_Sales',!Village,!Budget_Version,ATTRS('Month',!Month,'Previous_Month'),!Year,'BB Purchase Vol YTD') + ['BB Purchase Vol' ] ;

Feeders:

Code: Select all

['BB Purchase Vol']  =>['BB Purchase Vol YTD' ] ;

['BB Purchase Vol YTD']  =>
DB('BU_Sales',!Village,!Budget_Version,ATTRS('Month',!Month,'Next_Month'),!Year,'BB Purchase Vol YTD');

but no YTD happening.

[BigG]

ok, ignore last post, usual story rushed job, copy and paste, one dimension was missing. Works now.

Note I have added for Period 1 a conditional statement. Rule is:

Code: Select all

['BB Purchase Vol YTD','Measure Amount'] = N:
If(ATTRN('Month',!Month,'Month_Number') <= 1,['BB Purchase Vol' ],
DB('BU_Retirement_Sales', !Village_Product, !Budget_Version, 
ATTRS('Month',!Month,'Previous_Month'),!Year, 'BB Purchase Vol YTD', 'Measure Amount')+ ['BB Purchase Vol' ] );

feeders are:

Code: Select all

['BB Purchase Vol']  =>['BB Purchase Vol YTD' ] ;

['BB Purchase Vol YTD']  =>
DB('BU_Retirement_Sales', !Village_Product, !Budget_Version, 
ATTRS('Month',!Month,'Next_Month'),!Year, 'BB Purchase Vol YTD', 'Measure Amount') ;

Tell me if you see improvements, otherwise tHanks for your help

[captnknz]

Hi

Good work. I can't see any improvements, I would expect that you may / may not need to refine the conditional statement over time, but this is normal as the model changes.

There are alternative ways that require more complex formulas, but I think this is the cleanest from a logical / user perspective to follow.

You will just need to remember to maintain the months. We constantly forgot to maintain the months attributes, and became our mantra "have you checked the month's attributes" when a problem occurred and became our first stop in error tracing.

All the best