SCRIPT LOGIC - Rolling FORECAST

[Former Member]

Hello Friends,

I am atemtpping to generate a Script Logic on SAP BPC that replies all the data from a specific category to the next year, following one Factor previously definided.

Until now, I am able to do the forecast for all the next year (year +1), but the source is only one month. (December). And my porpuse is to get a source that are all the months from that year, divided by 12 (the 12 months of the year).

the script I have so far is this:

*XDIM_MEMBERSET TIME=%TIME_SET%

*SELECT(%INFLAT%,"RATE","P_A_CATEGORY","ID='FORECAST'")

*XDIM_MEMBERSET P_A_DATASRC=REC

*XDIM_MEMBERSET P_A_CATEGORY=PFA

*WHEN TIME

*IS %YEAR%.DEC

*FOR %MONTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

*REC(FACTOR = %INFLAT%, P_A_CATEGORY=FORECAST, TIME= "%YEAR%(+1).%MONTH%")

*NEXT

*ENDWHEN

*COMMIT

it works, but as I said it only considers as source the "%YEAR%.DEC" month.

My sugest is, continue with that month, but in a YTD prespective (but my application is PERIODIC), or consider all months, and divide the data by 12, or at the Factor, divide it by 12.

Waiting for your help.

Thanks in advance.

João Silva

[Former Member]

Hi

Your logic is looking good, slight change in your logic "*IS %YEAR%.DEC", try the logic given below.

*XDIM_MEMBERSET TIME=%TIME_SET%

*SELECT(%INFLAT%,"RATE","P_A_CATEGORY","ID='FORECAST'")

*XDIM_MEMBERSET P_A_DATASRC=REC

*XDIM_MEMBERSET P_A_CATEGORY=PFA

*FOR %MONTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

*WHEN TIME

*IS %YEAR%.%MONTH%

*REC(FACTOR = %INFLAT%, P_A_CATEGORY=FORECAST, TIME= "%YEAR%(+1).%MONTH%")

*NEXT

*ENDWHEN

*COMMIT

[Former Member]

Hi Rushendra,

First of all, thanks for your concern and for the tip.

However, when I try to validate in BPC the scipt you gave me, I got the following error message:

"Invalid when/endwhen - Line#14 : Invalid when/endwhen.

If you could help me with this, it would be great.

Once more, thanks for all the help.

João Silva

[Former Member]

Hi

Can you try it now:

*XDIM_MEMBERSET TIME=%TIME_SET%

*SELECT(%INFLAT%,"RATE","P_A_CATEGORY","ID='FORECAST'")

*XDIM_MEMBERSET P_A_DATASRC=REC

*XDIM_MEMBERSET P_A_CATEGORY=PFA

*FOR %MONTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

*WHEN TIME

*IS %YEAR%.%MONTH%

*REC(FACTOR = %INFLAT%, P_A_CATEGORY=FORECAST, TIME= "%YEAR%(+1).%MONTH%")

*ENDWHEN

*NEXT

*COMMIT

[Former Member]

Hello Rushendra,

Once more thanks for such a quick help.

Now the script is avaiable for validation, and it executes sucessfully at BPC.

Although now, the script only generates values for the month that I select in the pop-up. In the limit I can put all the 12 months and the program will generates data for all that 12 months.

However it will do directly month - to - monht.

Like this :

2011 - 2012

January 2011 - January 2011*(1.RATE)

February 2011 - February 2011*(1.RATE).

...

But want we need is that the script consider all the data from the 12 months, and replicate it for all the 12 months for year +1.

something like this.

For instance, for account 6423000000

January 2011 - 100 u20AC

March 2011 - 120 u20AC

November 2011 - 120 u20AC

(100 + 120 +120)/12= 28,3

So the script will generate this data for all the 12 months in the next year, multiplicated by the factor

The result will be:

January 2012 - 28,3 *(1.1)

February 2012 - 28,3*(1.1)

March 2012 - 28,3*(1.1)

April 2012 - 28,3*(1.1).

.....

For all the months until December.

Waiting for your help and replies.

Thank you in advance.

João Silva

[Former Member]

Hi,

I dont know whether I understood completely or not.

You want to take the values of all the months of the current year, calculate the average and then multiply this average value with the RATE and copy it to all the months of the next year. Is this what you want?

[Former Member]

Hello Nilanjan!

Thanks for the concern.

That's exactly what I want!!

Take the values of all the months, divide always by 12. Multiplicate by the rate factor, and then copy it for all the months of the next year. That's it!

Do you think you could help me?

Thank you in advance.

João Monteiro

[Former Member]

Hi,

I am not sure whether I can help or not, but surely can try.

Try using the below code:

*XDIM_MEMBERSET TIME=BAS(%YEAR%.TOTAL)
*SELECT(%INFLAT%,"RATE","P_A_CATEGORY","ID='FORECAST'")
*XDIM_MEMBERSET P_A_DATASRC=REC
*XDIM_MEMBERSET P_A_CATEGORY=PFA

*FOR %MONTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
   *WHEN ACCOUNT
   *IS *
      *REC(EXPRESSION = %YEAR%.TOTAL / 12 * %INFLAT%, P_A_CATEGORY=FORECAST, TIME= "%YEAR%(+1).%MONTH%")
   *ENDWHEN
*NEXT

*COMMIT

Hope this helps.

[Former Member]

Hello Nilanjan,

Conceptually, it seems very correct.

And think that's exactly what we are looking for.

Although when I try to validate the script, I get the following message: " Line 0 sintax error: " missing ; before statement"

Do you think you can solve this?

Once more, thank's for all the help.

Regards.

[Former Member]

Hi,

Can you please try:

*XDIM_MEMBERSET TIME=BAS(%YEAR%.TOTAL)
*SELECT(%INFLAT%,"RATE","P_A_CATEGORY","ID='FORECAST'")
*XDIM_MEMBERSET P_A_DATASRC=REC
*XDIM_MEMBERSET P_A_CATEGORY=PFA
 
*FOR %MONTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
   *WHEN ACCOUNT
   *IS *
      *REC(EXPRESSION = [TIME].[%YEAR%.TOTAL] / 12 * %INFLAT%, P_A_CATEGORY=FORECAST, TIME= "%YEAR%(+1).%MONTH%")
   *ENDWHEN
*NEXT
 
*COMMIT

Hope this helps.

[Former Member]

Hello Nilanjan,

I updated de Script and now the package is processing for 2h...

As soon as the system gives any answear, I will notify you.

Once more, Thank's for all the help and concern.

Regards,

João Silva

[Former Member]

Hi,

Its taking so much time, means that there are lot of records coming in from the application. So, you might try to restrict the code more to just check whether its working fine or not. You can restrict it more using XDIM statements.

Hope this helps.