Retrieve values from Prior Category

26 Views

Hi ,

I'm new to BPC scripts. Could someone be so kind to explain me the BPC scripting in regards to this task that i'm working on?

I need write a script to retrieve the periodic values from last three periods (eg, in FEB 2011, sum the periodic values of DEC 10, JAN 11 and FEB 11). Our values are all in YTD and the prior year is not in the same category (eg, for Budget C201, but the prior year category is Actual C100). Prior cateory is defined as a dimension property.

I tried following two scripts:

Script 1:

*PRIOR CATEGORY=CATEGORY.CATEGORY_FOR_OPE

*XDIM_MEMBERSET TIME=PRIOR(2),PRIOR,%TIME_SET%

*XDIM_MEMBERSET ACCOUNTL=<ALL>

*WHEN ACCOUNTL.DSO

*IS "PL_DSO"

*REC(EXPRESSION=GET(TIME=PRIOR,MEASURES="PERIODIC"),ACCOUNTL=RA001) // I tried also TIME=PRIOR(2) but it didn't work.

*ENDWHEN

This works only when the *REC setence is with GET(TIME=PRIOR) and in the same category. If it has to cross the category boundary, BPC generates some strange values, which i don't recognize.

Script 2:

*PRIOR CATEGORY=CATEGORY.CATEGORY_FOR_OPE

*BEGIN

#RA001=-(([ACCOUNTL].[PL202],[MEASURES].[PERIODIC])

+([ACCOUNTL].[PL202],[MEASURES].[PERIODIC],[TIME].PrevMember)

+([ACCOUNTL].[PL202],[MEASURES].[PERIODIC],[TIME].PrevMember.PrevMember))

*END

This correctly retrieves the periodic value and sums last three periods, but doesn't work when it has to retrieve from the prior year category.

It would be greatly appreciated if some one could please tell me what is wrong with my codes or provide me a code to solve my problem.

Many thanks.

Weiz

Edited by: Weizsu on Mar 1, 2011 2:19 PM