Avoid hard coding time periods in SAC Planning data actions

e_pos — Fri, 11 Oct 2024 11:47:06 GMT

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

Re: Avoid hard coding time periods in SAC Planning data actions

William_Yu1 — Sat, 12 Oct 2024 08:04:32 GMT

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)

Re: Avoid hard coding time periods in SAC Planning data actions

e_pos — Mon, 14 Oct 2024 06:55:16 GMT

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 !

Re: Avoid hard coding time periods in SAC Planning data actions

William_Yu1 — Mon, 14 Oct 2024 07:25:44 GMT

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

topic Re: Avoid hard coding time periods in SAC Planning data actions in Data and Analytics Forum

Avoid hard coding time periods in SAC Planning data actions

Re: Avoid hard coding time periods in SAC Planning data actions

Re: Avoid hard coding time periods in SAC Planning data actions

Re: Avoid hard coding time periods in SAC Planning data actions