Script Logic *REC with differents periods

[former_member407019]

Hi everyone,

I'am trying to do a script logic in SAP BPC 10.1 Standard with next parameters:

The target is for example, when the user select the month ABR.2021, I should have a package for the next;

EBITDA_MOVIL= VALUE EBITDA(DIC Previous year - VALUE EBITDA(ABR Previous year - VALUE EBITDA current year)

I made the next script, but i have a problem with the lookup;

//**********Calculo Ebitda Movil***********//

*SELECT(%ANIO%,YEAR, TIEMPO, ID = %TIEMPO_SET%)

*SELECT(%MESES%,ID, TIEMPO, ID <= %TIEMPO_SET% AND ID >= %ANIO%.01)

*XDIM_MEMBERSET FLUJO=F99

*XDIM_MEMBERSET AUDITORIA=BAS(ALL_AUDIT)

*XDIM_MEMBERSET INTERCOMPANY=BAS(TOTAL)

*XDIM_MEMBERSET GRUPO=BAS(TOT_GRUPOS)

*XDIM_MEMBERSET MONEDA=USD

*XDIM_MEMBERSET VERSIONES=%VERSIONES_SET%

*XDIM_MEMBERSET TIEMPO=%MESES%

*XDIM_MEMBERSET SOCIEDAD=%SOCIEDAD_SET%

*XDIM_MEMBERSET CUENTA=EBITDA

*LOOKUP CONSOLIDACION

*DIM T0:TIEMPO =TMVL(0,%TIEMPO_SET%)

*DIM T1:TIEMPO =TMVL(-1,%TIEMPO_SET%)

*DIM T2:TIEMPO =TMVL(-2,%TIEMPO_SET%)

*DIM T3:TIEMPO =TMVL(-3,%TIEMPO_SET%)

*DIM T4:TIEMPO =TMVL(-4,%TIEMPO_SET%)

*DIM T5:TIEMPO =TMVL(-5,%TIEMPO_SET%)

*DIM T6:TIEMPO =TMVL(-6,%TIEMPO_SET%)

*DIM T7:TIEMPO =TMVL(-7,%TIEMPO_SET%)

*DIM T8:TIEMPO =TMVL(-8,%TIEMPO_SET%)

*DIM T9:TIEMPO =TMVL(-9,%TIEMPO_SET%)

*DIM T10:TIEMPO =TMVL(-10,%TIEMPO_SET%)

*DIM T11:TIEMPO =TMVL(-11,%TIEMPO_SET%)

*DIM T12:TIEMPO =TMVL(-12,%TIEMPO_SET%)

*DIM MEASURES=PERIODIC

*DIM T0M:TIEMPO =TMVL(0,%TIEMPO_SET%)

*ENDLOOKUP

*WHEN CUENTA

*IS EBITDA

*WHEN TIEMPO.MONTHNUM

*IS 1

*REC(EXPRESSION=LOOKUP(T1)+(LOOKUP(T12)*-1)+LOOKUP(T0M),CUENTA=EBITDA_MOVIL)

*ENDWHEN

*ENDWHEN

*COMMIT

*WHEN CUENTA

*IS EBITDA

*WHEN TIEMPO.MONTHNUM

*IS 2

*REC(EXPRESSION=LOOKUP(T2)+(LOOKUP(T12)*-1)+LOOKUP(T0M),CUENTA=EBITDA_MOVIL)

*ENDWHEN

*ENDWHEN

*COMMIT

I did it just to test the first two months. Since this should work for twelve months, but depending on the month of selection. How can I make it dynamic and work?

I hope I have been clear with what I need. Thanks for your time and help

[former_member186338]

Can you provide a clear example of the logic using English month names?

[former_member407019]

Good day,

Yes, of course. Let me share the example:

If the user selects the next, the logic should do the next;

Selection = April 2021

Ebitda Movil= December 2020 - April 2020 + April 2021

Selección = May 2021

Ebitda Movil= December 2020 - May 2020 + May 2021

__________________________________________

I don't know if this is enough, I appreciate if you have any questions.

[former_member186338]

Then please accept the correct answer!

[former_member186338 · Accepted solution]

In general, you need to get the same month value in prior year and DECEMBER in prior year!

//%TIEMPO_SET%=2021.05
*SELECT(%Y%,[YEAR],TIEMPO,ID=%TIEMPO_SET%) //%Y%=2021
*SELECT(%M%,[MOTHNUM],TIEMPO,ID=%TIEMPO_SET%) //%M%=5
*SELECT(%TIDJAN%,[TIMEID],TIEMPO,YEAR=%Y% AND MONTHNUM=1 AND CALC=N) //%TIDJAN%=20210100
*SELECT(%TIDL%,[TIMEID],TIEMPO,TIMEID<%TIDJAN%) //%TIDL%=...20201100,20201200
*SELECT(%PREVYEAR%,[YEAR],TIEMPO,TIMEID<%TIDJAN% AND TIMEID>=%TIDL%) //2020

*SELECT(%PREVYEARDEC%,[ID],TIEMPO,YEAR=%PREVYEAR% AND MONTHNUM=12) //2020.12
*SELECT(%PREVYEARMONTH%,[ID],TIEMPO,YEAR=%PREVYEAR% AND MONTHNUM=%M%) //2020.05

//...
*XDIM_MEMBERSET CUENTA=EBITDA 
*XDIM_MEMBERSET TIEMPO=%TIEMPO_SET% 

*WHEN CUENTA 
*IS * 
  *REC(EXPRESSION=[TIEMPO].[%PREVYEARDEC%]-[TIEMPO].[%PREVYEARMONTH%]+%VALUE%,CUENTA=EBITDA_MOVIL) 
*ENDWHEN

P.S. Please read:

https://blogs.sap.com/2018/11/04/how-to-calculate-next-or-previous-time-member-in-bpc-script-logic/

[former_member186338 · Accepted solution]

Please use corrected script (with CALC=N added)

//%TIEMPO_SET%=2021.01
*SELECT(%Y%,[YEAR],TIEMPO,ID=%TIEMPO_SET%) //%Y%=2021
*SELECT(%M%,[MONTHNUM],TIEMPO,ID=%TIEMPO_SET%) //%M%=1
*SELECT(%TIDJAN%,[TIEMPOID],TIEMPO,YEAR=%Y% AND MONTHNUM=1 AND CALC=N) //%TIDJAN%=20210100
*SELECT(%TIDL%,[TIEMPOID],TIEMPO,TIEMPOID<%TIDJAN% AND CALC=N) //%TIDL%=...20201100,20201200
*SELECT(%PREVYEAR%,[YEAR],TIEMPO,TIEMPOID<%TIDJAN% AND TIEMPOID>=%TIDL%) //2020

*SELECT(%PREVYEARDEC%,[ID],TIEMPO,YEAR=%PREVYEAR% AND MONTHNUM=12 AND CALC=N) //2020.12
*SELECT(%PREVYEARMONTH%,[ID],TIEMPO,YEAR=%PREVYEAR% AND MONTHNUM=%M% AND CALC=N) //2020.01

*XDIM_MEMBERSET CUENTA=EBITDA 
*XDIM_MEMBERSET TIEMPO=%TIEMPO_SET% 
*XDIM_MEMBERSET VERSIONES=%VERSIONES_SET% 
*XDIM_MEMBERSET SOCIEDAD=%SOCIEDAD_SET% 

*WHEN CUENTA 
*IS * //already defined in scope!
  *REC(EXPRESSION=[TIEMPO].[%PREVYEARDEC%]-[TIEMPO].[%PREVYEARMONTH%]+%VALUE%,CUENTA=EBITDA_MOVIL) 
*ENDWHEN

[former_member407019]

Good day... Mr. Vadim, I appreciate it and thank you very much. I tested the script and ran it successfully.

[former_member186338]

Sorry, but correct answer is not accepted!

[former_member407019]

Mr Vadim,

I really appreciate your help. I've been looking at the logic but a problem still occurs.

I made some changes, but I can't get it to work.