SAC Data Action: Implementing a Rolling Forecast without cumulative DATA() behavior or unbooked data

[#1]

Hi SAP Community,

I am trying to implement a standard Rolling Forecast logic inside an SAC Planning Advanced Formula Data Action.

The Goal:

When planning for a specific target year (e.g., %Ziel_Jahr% = 2026), the Data Action should prepare the Plan version by:

1. Copying existing Actual data for all elapsed months of the current year (e.g., Jan–Jul).

2. For all remaining/future months of the target year where no actuals exist yet, copying the previous year's actuals (PREVIOUS(12, "MONTH")).

3. Keeping the configuration simple: Using only 1 parameter (%Ziel_Jahr%) without requiring planners to enter cut-off months manually.

The Code: 

MEMBERSET [d/Measures] = "gemeldete_Umsaetze"
MEMBERSET [d/Datum] = BASEMEMBER([d/Datum].[h/YM], %Ziel_Jahr%)

DELETE()

IF RESULTLOOKUP([d/Version] = "public.Actual") > 0 THEN
DATA() = RESULTLOOKUP([d/Version] = "public.Actual")
ELSE
DATA() = RESULTLOOKUP([d/Version] = "public.Actual", [d/Datum] = PREVIOUS(12, "MONTH"))
ENDIF

The Problems Encountered:

1. Unbooked Data (NULL vs 0😞

   • Future months (Sep–Dec) do not have booked records in Actual (displayed as – / unbooked, not numerical 0).

   • Because of sparse data traversal, the ELSE branch is completely ignored for these months, leaving future plan months empty.

   • Direct comparisons like IF RESULTLOOKUP(...) == 0 or ISNULL(...) fail with parser errors (RESULTLOOKUP cannot be used in the left operand).

2. Additive DATA() behavior (+=😞

   • If I initialize the entire year first with previous year's actuals and then try to overwrite elapsed months with current actuals:

DATA() = RESULTLOOKUP([d/Version] = "public.Actual", [d/Datum] = PREVIOUS(12, "MONTH"))

IF RESULTLOOKUP([d/Version] = "public.Actual") > 0 THEN
DATA() = RESULTLOOKUP([d/Version] = "public.Actual")
ENDIF

• • SAC sums the values up instead of overwriting them (e.g., Jan Actual 12,138 + Jan LY 12,940 = 25,078 in Plan).

  • Subtracting the prior year value inside the IF block fails because intermediate buffer states or underlying dimension mismatches prevent a clean cancellation.

• Date Range / Hierarchy limitations:

  • Script functions like TO, FIRST, or date offsets either reject cross-hierarchy usage (Year parameter vs. Month scope) or throw parser errors when combined with parameters.

Question:

What is the recommended, robust pattern in SAC Advanced Formulas to populate a rolling forecast (Current Actuals where present, LY Actuals where absent) in a single Data Action using only the Target Year parameter, without triggering cumulative addition or memory overflow?

Any insights or best-practice patterns would be greatly appreciated!

[William_Yu1]

Hi Hoppeno, 

  With SAC advance formula, by default DATA() will get booked value from Actual version. So the IF clause is not needed.  Single line as below will achieve what you requested. 

DATA() = RESULTLOOKUP([d/Version] = "public.Actual")

  And standard copy step also results the same, no coding with AF needed. 

Best regards, William 

[N1kh1l]

@Hoppeno 

I think there is an assumption you have made that granularity is exactly same for current year actuals, plan and last year actuals which is generally not true. That explains the additive behavior. One of the below variant should work.

MEMBERSET [d/Measures] = "gemeldete_Umsaetze"
MEMBERSET [d/Datum] = BASEMEMBER([d/Datum].[h/YM], %Ziel_Jahr%)

DELETE()

DATA() = RESULTLOOKUP([d/Version] = "public.Actual", [d/Datum] = PREVIOUS(12, "MONTH")) //copy LY Actuals to all periods

IF RESULTLOOKUP([d/Version] = "public.Actual") !=NULL THEN // check if CY Actuals exists

DELETE() //clear the period

DATA() = RESULTLOOKUP([d/Version] = "public.Actual") // Copy CY Actuals into the period
ENDIF

OR the below

MEMBERSET [d/Measures] = "gemeldete_Umsaetze"
MEMBERSET [d/Datum] = BASEMEMBER([d/Datum].[h/YM], %Ziel_Jahr%)

DELETE()

DATA() = RESULTLOOKUP([d/Version] = "public.Actual") // Copy CY Actuals to periods

IF RESULTLOOKUP([d/Version] = "public.Actual", [d/Datum] = PREVIOUS(12, "MONTH")) +RESULTLOOKUP()=RESULTLOOKUP([d/Version] = "public.Actual", [d/Datum] = PREVIOUS(12, "MONTH")) THEN // Check if LY actuals + current Year plan =LY actuals, would be true for empty periods

DATA() = RESULTLOOKUP([d/Version] = "public.Actual",[d/Datum] = PREVIOUS(12, "MONTH")) // Copy LY actuals
ENDIF

Hope this helps!

Nikhil