Accumulate Measure up to Prior Period

[CiskoWalt]

Hello,

Would ilke to set up a rule that summarizes data for all previous periods. For example, when the current period is Jan-2010, then the meausue 'Accume Value' should summarize the Value for all months before Jan-2011 and not include future periods, Feb 2011 below.

Value Accume value
Oct 2010 10 0
Nov-2010 20 10
Dec-2010 30 30
Jan-2011 40 60
Feb-2011 50 0


To do this, I set up an a numeric attribute on the Month dimension named ElementIndex. The thought is I can get the index of the current Month element and only summarize the values where the data has a Month element that is less than the CurrentMonthIndex.

I am aware of the DIMIX and DIMNM functions; but I wany to stay away from them incase the elememtns are reindexed in the future.

['CurrentMonthIndex'] = ATTRN('Month', !Month, 'ElementIndex');

['ITD Variance to Snapshot'] =
IF(CurrentMonthIndex'] < ATTRN('Month', !Month, 'ElementIndex'),
DB('MonthlyAnalysis', 'All Years', !Entity, !MSO, !Titles, !Format, !MonthlyBalType,'Rentrak Variance to Snapshot'), 0);

This will not work, since the ATTRN function returns the same index value.

Appreciate you help.

Thanks

Walt

[declanr]

Walt,

Good idea to try and avoid the dimix/dimnm combo within rules but I am not entirely sure what your index attribute is doing... I am just going to focus on the first part of your post:

Would ilke to set up a rule that summarizes data for all previous periods. For example, when the current period is Jan-2010, then the meausue 'Accume Value' should summarize the Value for all months before Jan-2011 and not include future periods, Feb 2011 below.

Value Accume value
Oct 2010 10 0
Nov-2010 20 10
Dec-2010 30 30
Jan-2011 40 60
Feb-2011 50 0

I may of course have missed something important by only looking at this part.

Step 1/ Set up an attribute for "Prior Period" (Next period would also be useful for the feeding side)

Step 2/ Create the rule:

['Accume Value']=N:
DB(Cube, Attrs ('Period',!Period,'Prior Period'),'Accume Value') + ['Value'];


The obvious problem with this is the "first period" conundrum that can be solved via a few methods, the easiest being put another rule (above the one in step 2 - order is important.)

If the first period was Oct-2012 then the rule would simply be:

['Oct-2012','Accume Value']=0;


Just make sure you update the attribute whenever new periods are created.

If I was doing this the first period would be set via control cube rather than hard-coding into the rule but the above should be sufficient.

[declanr]

On re-reading I think I worked out what you were trying to do.


And on the reason that your rule isn't doing what you want...

If you put !Dimension in a rule; it pulls the element within that dimension for which the cell reference relates. It does not cycle the whole dimension, in order to cycle dimensions you need to go down the TI route as opposed to a rule

Instead of:

['CurrentMonthIndex'] = ATTRN('Month', !Month, 'ElementIndex');

You would need to replace the !Month with a cell reference to a control cube that is always holding the "Current Month" and gets updated every roll-forward.

['CurrentMonthIndex'] = ATTRN('Month', DB('z_control','Current Month','String Value'), 'ElementIndex');

If you are using a version dimension it would also be necessary to filter that in.

HTH