Script logic to calculate average value on nodes - SAP BPC NW 10.0

[Former Member]

Hi experts,

I need to have in the parent members of the dimension TIME (2014.Q1, 2014.Q2,  2014.Q3, 2014.Q4, 2014.TOTAL) the average value of  their children and not the sum. For example :

2014.01        2014.02         2014.03        2014.Q1

    1                  2                    3                  2                                          

2 = AVG(1, 2, 3)

I tried the script bellow but it doesn't work , it throws the error " ReferenceError : AVG is not defined"

*SELECT(%TIMESET%,"[ID]",TIME,"[CALC]='Y'")

*XDIM_MEMBERSET TIME = %TIMESET%

*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN DIM1

*IS C02

*WHEN TIME

*IS %TIMESET%

*REC(EXPRESSION = AVG(Descendants([%TIMESET%].CURRENTMEMBER)), TIME = %TIMESET%)

*ENDWHEN

*ENDWHEN

*COMMIT

How can I achieve this ?

Thanks

Maha

[former_member186338 · Accepted solution]

Hi Maha,

Please, read my document

Using script logic for average is not the good approach.

B.R. Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your reply

Your document is very interesting. However, if I use member formulas, I ll have to specify formulas manually for all TIME nodes (2014 nodes, 2015 nodes, 2016 nodes, ...) which is not a god solution for me. Please correct me If I'm wrong.

Regards

Maha

[former_member186338 · Accepted solution]

Yes, you are wrong

Please, explain your requirements:

You have to calculate average for some accounts? What are the figures - business value?

Vadim

[Former Member · Accepted solution]

I have to calculate the average of "productivity per hectare" of a certain product by quarters. Which means that the user enters the values of this KPI by months and needs to have the average calculated by quarters.

The average should be calculated for one account only.

Thanks

Maha

[former_member186338 · Accepted solution]

The best way is to enter 2 accounts: Productivity and Area, calculating by dimension member formula:

ProductivityPerHectare=IIF([Area]=0,NULL,[Productivity]/[Area])

If you have to enter ProductivityPerHectare and Area, then in script you can calculate Productivity:

*WHEN ACCOUNT

*IS Area

*REC(EXPRESSION=%VALUE%*[ACCOUNT].[ProductivityPerHectare],ACCOUNT=Productivity)

*ENDWHEN

*WHEN ACCOUNT

*IS ProductivityPerHectare  //user input

*REC(EXPRESSION=%VALUE%*[ACCOUNT].[Area],ACCOUNT=Productivity)

*ENDWHEN

Then dimension member formula:

ProductivityPerHectareCalc=IIF([Area]=0,NULL,[Productivity]/[Area])

You can use arithmetic average of ProductivityPerHectare only if Area is always constant.

In this case you can create some dummy account member DUMMY and fill it with 1:

*WHEN ACCOUNT

*IS ProductivityPerHectare  //user input

*REC(EXPRESSION=1,ACCOUNT=DUMMY)

*ENDWHEN

Then dimension member formula:

ProductivityPerHectareCalc=IIF([DUMMY]=0,NULL,[ProductivityPerHectare]/[DUMMY])

Vadim

[Former Member · Accepted solution]

Thank you very much Vadim,

I have just a little remark; I don't see any TIME Dimension in the script ! And this is the report needed :

                                                   2014.01        2014.02         2014.03        2014.Q1

Area                                                10                20                       30                 60  

ProductivityPerHectare             1                    2                         3                  2       

Productivity                                    10                40                       90               140

Regards,

Maha

[former_member186338 · Accepted solution]

You don't need any TIME dimension in the dimension member formula or in some script. The dimension member formula will correctly calculate result for any period (base or parent):

2014.01 10/10=1

2012.02 40/20=2

2014.03 90/30=3

2014.Q1=(10+40+90)/(10+20+30)=140/60=2,333

2,333 - is a correct weighted average, 2 - incorrect arithmetic average!

Vadim

[Former Member · Accepted solution]

Ok, Thanks a lot for your help.

Best Regards

Maha

[Former Member · Accepted solution]

Hi Vadim,

I tried your solution above, but I still get the sum in the parents not the average !

                                       2014.01          2014.02        2014.03   2014.Q1

Area                                            12                         3                        10           35 

ProductivityPerHectare         0,9166                0,76                  1,2          2,885          

Productivity                                11,0000            10,0000             12            33

Regards

Maha

[former_member186338 · Accepted solution]

For input you have to use: ProductivityPerHectare (and this member will aggregate as usual - sum on parent).

For reporting - another member with dimension member formula (root base member):

ProductivityPerHectareCalc=IIF([DUMMY]=0,NULL,[Productivity]/[Area])

Vadim