Skip to Content
0
Former Member
May 21, 2015 at 07:08 PM

Average Calculation for Multiple months selection in BPC 10

153 Views


Hello Experts,

I have the below requirement to calculate average FTE accounts based on FTE accounts for Multiple months selection.

Example: we have the below values for Account "ABC" .

2014.01 2014.02 2014.03 2014.04 2014.05 2014.06 2014.07 2014.08 10 20 30 40 50 60 70 80

The expected result for "ABC_AVG" acount like below .

For Jan =Jan/1

Feb =(Jan+feb)/2

March =(jan+feb+March)/3

April =(Jan+Feb+march+April)/4

May =(Jan+Feb+march+April+May)/5 etc.

2014.01 2014.02 2014.03 2014.04 2014.05 2014.06 2014.07 2014.08 2014.09 2014.10 10/1 15 20 50 30 35 40 45 45 45

I have written the below code to acheive the result and it wokring fine most of the cases ,however if any time period don't have value,then no calculation happening.This is wrong for my requirement.

Basically I'm new to script.So,please help/suggest how to acheive this result in all cases. If anybody provide better code then it will be reallly help me.

Note: YTD and QTD functionality not working in my environment.

*FOR %STM% = %TIME_SET%

*XDIM_MEMBERSET ACCOUNT = ABC
*XDIM_MEMBERSET TIME = %STM%

*WHEN TIME.MONTHNUM
*IS 1
*REC(EXPRESSION = %VALUE%,ACCOUNT = ABC_AVG)

*IS 2
*REC(EXPRESSION = (%VALUE%+[TIME].[TMVL(-1,%STM%)] )/2,ACCOUNT = ABC_AVG)

*IS 3
*REC(EXPRESSION = (%VALUE%+[TIME].[TMVL(-1,%STM%)]+[TIME].[TMVL(-2,%STM%)])/3,ACCOUNT = ABC_AVG)

*IS 4
*REC(EXPRESSION = (%VALUE% +[TIME].[TMVL(-1,%STM%)]+[TIME].[TMVL(-2,%STM%)]+[TIME].[TMVL(-3,%STM%)])/4,ACCOUNT = ABC_AVG)

*IS 5
*REC(EXPRESSION = (%VALUE% +[TIME].[TMVL(-1,%STM%)]+[TIME].[TMVL(-2,%STM%)]+[TIME].[TMVL(-3,%STM%)]+[TIME].[TMVL(-4,%STM%)])/5,ACCOUNT = ABC_AVG)

*IS 6
*REC(EXPRESSION = (%VALUE% +[TIME].[TMVL(-1,%STM%)]+[TIME].[TMVL(-2,%STM%)]+[TIME].[TMVL(-3,%STM%)]+[TIME].[TMVL(-4,%STM%)]+[TIME].[TMVL(-5,%STM%)])/6,ACCOUNT = ABC_AVG)

*IS 7
*REC(EXPRESSION = (%VALUE% +[TIME].[TMVL(-1,%STM%)]+[TIME].[TMVL(-2,%STM%)]+[TIME].[TMVL(-3,%STM%)]+[TIME].[TMVL(-4,%STM%)]+[TIME].[TMVL(-5,%STM%)]+[TIME].[TMVL(-6,%STM%)])/7,ACCOUNT = ABC_AVG)


*ENDWHEN

*NEXT
*COMMIT

Thanks,

Pramod