Growth Rate in Script Logic

[Former Member]

Hello,

I have a requirement from a client that I want to implement in script logic, however all the approaches I've tried don't work.

The rule is fairly simple, to calculate the monhtly budget values based on the actual values of the previous year and a growth rate that is stored in the months of the budget year.

Example (only for the first 3 months of the year to simplify:

                   ACTUAL_VALUES     GROWTH_RATE      BUDGET_VALUES

2014 Jan      100

2014 Feb      200

2014 Mar      500

2014 Apr      350

2015 Jan                                       0,1                                 10

2015 Feb                                      0,6

2015 Mar                                      0,3

2015 Apr                                       0,4

I'm trying with the following script logic in the transaction UJKT, but I've only managed to perform the calculation correctly for one month. I need to insert a data region for the PERIOD dimension, but when I add more than one month (for example PERIOD = 2014.01,2014.02,2014.03,2014.04) the calculation only consider one of the months in the %PERIOD_SET% and stores everything in the same month.

*XDIM_MEMBERSET RUBRICA_INV_DIV = ACTUAL_VALUES, BUDGET_VALUES

*WHEN RUBRICA_INV_DIV

*IS ACTUAL_VALUES

*REC(RUBRICA_INV_DIV= BUDGET_VALUES,PERIOD = TMVL(12,%PERIOD_SET%), EXPRESSION = %VALUE% * ([RUBRICA_INV_DIV].[GROWTH_RATE],[PERIOD].[TMVL(12,%PERIOD_SET%)]))

*ENDWHEN

*COMMIT

I need to use the ACTUAL_VALUES in the WHEN command because this account has other details in other dimensions, that the growth rate doesn't have and I need it in the budget results.

Can anyone help me please?

Thanks!

[former_member186338 · Accepted solution]

Hi Lara,

Easy!

*XDIM_MEMBERSET RUBRICA_INV_DIV=ACTUAL_VALUES //Don't scope target - useless!

*FOR %PER%=%PERIOD_SET%

*WHEN RUBRICA_INV_DIV

*IS * //ACTUAL_VALUES

*REC(EXPRESSION=%VALUE% * ([RUBRICA_INV_DIV].[GROWTH_RATE],[PERIOD].[TMVL(12,%PER%)]),RUBRICA_INV_DIV= BUDGET_VALUES,PERIOD = TMVL(12,%PER%))

*ENDWHEN

*NEXT

//Don't use useless COMMIT!

Vadim

[former_member186338 · Accepted solution]

P.S. It's possible to increase the execution speed of the script by removing FOR/NEXT loop and using some new property of PERIOD dimension with LOOKUP etc... But for up to 12 months the script will be OK even with FOR/NEXT.

[Former Member · Accepted solution]

Thanks Vadim! It worked perfectly!!

Can you explain to me in detail the approach you were suggesting about the property and the LOOKUP? Even though I don't have perform issues now, it can be an issue later in the project.

Thanks again!!

[former_member186338 · Accepted solution]

You can create a property like NEXTYEARPER and fill it:

ID            NEXTYEARPER

2014.01 - 2015.01

2014.02 - 2015.02

...

Then you can use the following script:

*LOOKUP CuurentModel //use real name!

*DIM NY: PERIOD=PERIOD.NEXTYEARPER

*DIM RUBRICA_INV_DIV="GROWTH_RATE"

*DIM MEASURES=PERIODIC //Or YTD - what is the default MEASURE of Current Model

*ENDLOOKUP

*XDIM_MEMBERSET MEASURES=PERIODIC //Or YTD - what is the default MEASURE of Current Model

*XDIM_MEMBERSET RUBRICA_INV_DIV=ACTUAL_VALUES

*WHEN RUBRICA_INV_DIV

*IS * //ACTUAL_VALUES

*REC(EXPRESSION=%VALUE% * LOOKUP(NY),RUBRICA_INV_DIV= BUDGET_VALUES,PERIOD = PERIOD.NEXTYEARPER)

*ENDWHEN

Vadim

P.S. Lines with MEASURES are required...

[Former Member · Accepted solution]

Thank you very much Vadim!

[Former Member · Accepted solution]

Hello Vadim,

In SAP BPC NW 10 it is not possible to define multiple lookup keys inside the same lookup?

For a similar calculation, I need to use the growth rate stored in 2015 Dec and 2016 Dec, so I tried to use your approach, like this:

*LOOKUP CuurentModel

*DIM NYFIRST: PERIOD=2015.12

*DIM NYSECOND: PERIOD=2016.12

*DIM RUBRICA_INV_DIV="GROWTH_RATE"

*DIM MEASURES=PERIODIC

*ENDLOOKUP

*XDIM_MEMBERSET MEASURES=PERIODIC

*XDIM_MEMBERSET RUBRICA_INV_DIV=ACTUAL_VALUES

*WHEN RUBRICA_INV_DIV

*IS *

*REC(EXPRESSION=%VALUE% * LOOKUP(NYFIRST) * LOOKUP(NYSECOND)),RUBRICA_INV_DIV= BUDGET_VALUES)

*ENDWHEN

But the result is null. I've performed other tests and it seems that the only lookup that has retrieves values in the one defined last, in this case NYSECOND. If I change the order (like below), the one with value is NYFIRST.

*LOOKUP CuurentModel

*DIM NYSECOND: PERIOD=2016.12

*DIM NYFIRST: PERIOD=2015.12

*DIM RUBRICA_INV_DIV="GROWTH_RATE"

*DIM MEASURES=PERIODIC

*ENDLOOKUP

Is this the usual behavior? Are there any workaround?

Thanks!

Lara

[former_member186338 · Accepted solution]

It's a known bug! Old BPC SP - please upgrade!

[former_member186338 · Accepted solution]

Look here:

[Former Member · Accepted solution]

Thank you Vadim, I've applied the SAP note and it works fine now!