Previous Members

[ablancof]

Hi people, i need your advice

This is the situation

I have a Date Dimension with 4 levels. This is the structure Year->Quarter->Month-> Day and i have a measure called Rolling Year Sales= Sales to the previous 12 months. I get you an example
suppose that the current date is 2013-05-10, then the Rolling Year Sales is the sum of Sales for this months: April 13, March 13, Feb 13, Jan 13, Dec 12, Nov 12, Oct 12, Sep 12, Aug 12, Jul 12, Jun 12, May 12 .

Then i tried to use the functions DIMIX and DIMNM to move between its members BUT these functions works with index, then my months are consolidated elements and their index aren't consecutive . Example Feb 13 has index 57 and March 89, then i dont know how to do! I need to move between these months to sum sales their Sales.

Any suggestion !
Sorry for my english grammar
Thanks in advance

[declanr]

There are a number of ways you could go about it but I personally think one of the easiest is to simply create consolidations for each months rolling balance e.g:

Code: Select all

C - 'Roll - May 13'
 'Apr 13' (Weight 1)
 'Mar 13' (Weight 1)
 'Feb 13' (Weight 1)
....
 'May 12' (Weight 1) 

And then you can have a simple rule along the lines of:

Code: Select all

['Rolling Balance']=N: DB( Cube, !Dim1, 'Roll - ' | Attrs ( 'Period', !Period, 'Month'), !Dim3, 'Value' )

You can create the consolidations through a basic TI.

Edit - The ATTRS is to show how you would pull the month from the day level period element if the cube in question actually goes down to day level, remember you would need to have a different rule at consolidated level so that the Rolling Value for 'May - 13' isn't the equivalent of 31 * the value.

[ablancof]

HI declanr, thanks for your answer but i cant alter the dimension and this calculation is to every day, this is the granularity.

I good idea but in this case it is not possible

Thanks

[declanr]

HI declanr, thanks for your answer but i cant alter the dimension and this calculation is to every day, this is the granularity.

I good idea but in this case it is not possible

Thanks

Why can't you alter the dimension? This would just be adding extra elements which can even be hidden, it would make no change to what is already there.

Assuming you can't make any structural changes and can only add rules (and hopefully add attributes?) you could add 12 attributes to your dimension:
"Prior Month 1"
"Prior Month 2"
...
"Prior Month 12"

And against a May-13 element, "Prior Month 1" would obviously be "Apr-13" through to "Prior Month 12" being "May-12" and your rule would be a somewhat cumbersome:

Code: Select all

['Rolling']=N: DB(Cube,!Dim1, Attrs ( 'Period', !Period, 'Prior Month 1' ), 'Value' ) + DB(Cube,!Dim1, Attrs ( 'Period', !Period, 'Prior Month 2' ), 'Value' ) ..... + DB(Cube,!Dim1, Attrs ( 'Period', !Period, 'Prior Month 12' ), 'Value' )

[ablancof]

Ok Ok let me check your idea, thank you so much