Avoid hard coding time periods in SAC Planning data actions

[e_pos]

We have the following data action to make a forecast for the next remaining months based on the index of the previous months. The index is calculated on past months. This index is used for the calculation of the future months. The below script will calculate the specific "PERIOD_FORECAST" (for example period 10 or 11 or 12) by multiplying the Budget for period 10 times the calculated index over the past 9 periods:

// Context

MEMBERSET [d/Measures] = "AMOUNT"

MEMBERSET [d/BEDRIJFSNR] = %COMPANY_FILTER%

MEMBERSET [d/Date] = %PERIOD_FORECAST%

MEMBERSET [d/NIV4] = %RUBRIEK%

// Empty data

DATA([d/Date] = %PERIOD_FORECAST%) = 0

// Formules

DATA([d/Date] = %PERIOD_FORECAST%) = 

((RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202301") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202302") 

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202303") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202304") 

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202305") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202306")

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202307") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202308")

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = "202309"))

/

(RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202301") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202302") 

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202303") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202304") 

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202305") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202306")

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202307") + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202308")

+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = "202309")))

* RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = %PERIOD_FORECAST%)

We would like to avoid the hard coded "2023xx" and replace it with a flexible solution

[e_pos]

Thanks a lot for this very usefull information. Would it be possible to make the "9" in the code also as a parameter to make it flexible ? In the next month it should then be "10". Many Thanks !

[William_Yu1]

Maybe you can try with something like this: 

MEMBERSET [d/Date] = PREVIOUS(9,"MONTH", %PERIOD_FORECAST%) TO %PERIOD_FORECAST%
MEMBERSET ...

VARIABLEMEMBER #PERIOD_VERSIE OF [d/Date]
VARIABLEMEMBER #PERIOD_VERSIE_VERGELIJK OF [d/Date]

DATA([d/Date] = #PERIOD_VERSIE) = RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%)
DATA([d/Date] = #PERIOD_VERSIE_VERGELIJK) = RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%)

DATA( [d/Date] = %PERIOD_FORECAST%) = RESULTLOOKUP([d/Date] = #PERIOD_VERSIE)/RESULTLOOKUP([d/Date] = #PERIOD_VERSIE_VERGELIJK)

[e_pos]

Thanks a lot for this very usefull information. Would it be possible to make the "9" in the code also as a parameter to make it flexible ? In the next month it should then be "10". Many Thanks !

[William_Yu1]

of course, you can make it as parameter as well if needed.