Analytics Software

Hi there

We have a business requirement to calculate a rolling 12 month to date value. That is, for any month, sum up the last 12 months of data. E.g. for June 2011, its May 2010 -> June 2011. We have the standard Year and Period dimension, and the member which holds this calculation is in another dimension called TimeView. TimeView just holds dynamic calculations for Month To Date(MTH), Year To Date (YTD) etc.

The problem I am having is because we have the standard Year and Period dimensions so I need to do a cross dimensional sum. All the solutions I am trying either give me an insufficient dynamic calc cache (increase lock block setting) or something similar. Our lock block setting is quite high and it doesnt seem to make a difference when I increase it.

Anyway, I have tried a few solutions:

Short one:

@SUMRANGE("MTH",@MDSHIFT(@CURRMBRRANGE(Period,LEV,0,1,),-1,"Year",)) + @SUMRANGE("MTH",@CURRMBRRANGE(Period,LEV,0,,0));

Long one:
IF(@ISUDA(ACCOUNT,"Flow"))
IF(@ISMBR(Jan))
YTD->Jan + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Jan,-1,"Year",);
ELSEIF(@ISMBR(Feb))
YTD->Feb + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Feb,-1,"Year",);
ELSEIF(@ISMBR(Mar))
YTD->Mar + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Mar,-1,"Year",);
ELSEIF(@ISMBR(Apr))
YTD->Apr + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Apr,-1,"Year",);
ELSEIF(@ISMBR(May))
YTD->May + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->May,-1,"Year",);
ELSEIF(@ISMBR(Jun))
YTD->Jun + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Jun,-1,"Year",);
ELSEIF(@ISMBR(Jul))
YTD->Jul + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Jul,-1,"Year",);
ELSEIF(@ISMBR(Aug))
YTD->Aug + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Aug,-1,"Year",);
ELSEIF(@ISMBR(Sep))
YTD->Sep + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Sep,-1,"Year",);
ELSEIF(@ISMBR(Oct))
YTD->Oct + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Oct,-1,"Year",);
ELSEIF(@ISMBR(Nov))
YTD->Nov + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Nov,-1,"Year",);
ELSEIF(@ISMBR(Dec))
YTD->Dec
ELSEIF(@ISMBR(Q1))
YTD->Mar + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Mar,-1,"Year",);
ELSEIF(@ISMBR(Q2))
YTD->Jun + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Jun,-1,"Year",);
ELSEIF(@ISMBR(Q3))
YTD->Sep + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Sep,-1,"Year",);
ELSEIF(@ISMBR(Q4))
YTD->Dec
ELSEIF(@ISMBR(H1))
YTD->Jun + @MDSHIFT(YTD->Dec,-1,"Year",) - @MDSHIFT(YTD->Jun,-1,"Year",);
ELSEIF(@ISMBR(H2))
YTD->Dec
ELSEIF(@ISMBR("YearTotal"))
YTD->Dec
ENDIF
ELSEIF(@ISUDA(ACCOUNT,"First"))
MTH->Jan;
ELSE
MTH;
ENDIF

I am sure this may be a common requirement across companies - does anyone know a solution to this?

Much appreciated!