BPC Script Logic - Dynamically calculating rolling averages

205 Views

Hi

I'm trying to develop a script that dynamically calculates an account average for the previous 12 months (not necessarily a calendar year) and writes it to the following 12 months (current selection per the action pane and the following 11 months)

I'm really new to BPC script writing, and seem to be having issues with the syntax.

I came across some sample code that calculated the average of 2009 (hardcoded and calendar year), and wrote it to 2010 (again, hardcoded and calendar year)

*SELECT(%SRCTIME%,"ID","TIME","[YEAR]=2009")
*XDIM_MEMBERSET ACCOUNT=CASH                           //cash for example purposes
*XDIM_MEMBERSET TIME=%SRCTIME% 
 
*WHEN ACCOUNT 
*IS CASH
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.JAN)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.FEB)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.MAR)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.APR)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.MAY)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.JUN)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.JUL)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.AUG)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.SEP)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.OCT)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.NOV)
     *REC(EXPRESSION=%VALUE%/12,TIME=2010.DEC)
*ENDWHEN

I am trying to figure out how to dynamically figure out what months to read from and write to

Someone suggested the lag function. Would this make my source selection as follows:

*SELECT(%SRCTIME%,"ID","TIME",[TIME].currentmember.lag(1) ,[TIME].currentmember.lag(2) ,[TIME].currentmember.lag(3)
 ,[TIME].currentmember.lag(4) ,[TIME].currentmember.lag(5) ,[TIME].currentmember.lag(6) ,[TIME].currentmember.lag(7) 
,[TIME].currentmember.lag(8) ,[TIME].currentmember.lag(9) ,[TIME].currentmember.lag(10) ,[TIME].currentmember.lag(11) 
,[TIME].currentmember.lag(12))

And how do I define the month id for the REC statements?

TIME=[TIME].currentmember, [TIME].currentmember.next(1), [TIME].currentmember.next(2)

etc... ?

Would appreciate any tips you guys could give me

Thanks

Edited by: TXAG07 on Mar 30, 2010 11:02 PM