Average Calculation for Multiple months selection in BPC 10

[Former Member]

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

[former_member186338]

Hi Pramod,

Why do you need the script calculations at all? Why not to use dimension member formula?

By the way it's not a good idea to to create a discussion that do not require the answer...

Vadim

[Former Member]

Hi Vadim,

at present  user calculating this averages manually with the help of reports and input template.He want to automate this process via Script logic.So,that he can execute this script any time he required.

Thanks,

Pramod

[former_member186338]

Ups, do you understand what I am talking about - "dimension member formula"?

Vadim

P.S. "YTD and QTD functionality not working in my environment." - but how??????

[Former Member]

Hi Vadim,

Sorry for confusion ,I know Dimension formulas functionality.Actually I have not given full requirement details. For seeking help for build script logic  just I have shared only one account. Below is the  actual requirement.

i) We have 70+ categories

ii) We have 8000+ costcenters

iii) Time period

iv) Profitcenters

v) 15 accounts

Based on category ,time period selection we have to calculate costcenter wise account average.

With this multiple selection criteria  we cannot go for Dimension Formulas.

For YTD issue:

I have Written

[MEASURES].[YTD] in REC statement but it's not working YTD wise.still it's behave PERIODIC.

Even we  generate the report and change it to YTD and QTD still it's showing the values in Periodic only.

We are currently with BPC SP14.

Thanks,

Pramod

[former_member186338]

Sorry, but you are still not providing your requirements!

"Based on category ,time period selection we have to calculate costcenter wise account average.

With this multiple selection criteria  we cannot go for Dimension Formulas." Why not????

"Even we  generate the report and change it to YTD and QTD still it's showing the values in Periodic only." - you have serious issues with BPC setup. YTD measure have to show proper results for INC/EXP account. Useless to discuss anything with issues like this.

Vadim