Page 1 of 1
YTD rule calculation?
Posted: Tue Feb 08, 2011 11:30 pm
by 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
Re: YTD rule calculation?
Posted: Tue Feb 08, 2011 11:58 pm
by 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
Re: YTD rule calculation?
Posted: Thu Feb 10, 2011 11:00 pm
by 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.
Re: YTD rule calculation?
Posted: Thu Feb 10, 2011 11:18 pm
by 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
Re: YTD rule calculation?
Posted: Fri Feb 11, 2011 12:52 am
by 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