SAC Data Action Advanced Formula - dynamic Average

[Marm]

Hello everyone,

in SAC, the data that is copied from actual to FC version should be extrapolated.

Example:

Actual were copied into FC version until February. The extrapolation was carried out from March to December, based on average of Actual.

The number of copied ACTUAL months is entered in the popup. The variable is used to calculate the average.

The following formula is used:

MEMBERSET [d/Measures] = "Z_S4_HSL"

MEMBERSET [d/Date] = [d/Version].[p/StartDate] TO [d/Version].[p/EndDate]

VARIABLEMEMBER #DATE OF [d/Date]

//-----------------------------------------------------------------------------------

// Calculate Average of ActualValue

DATA([d/Measures] = "Z_S4_HSL",[d/Date] = #DATE) = RESULTLOOKUP([d/Measures] = "Z_S4_HSL") / %ZMM_ACT_PERIOD_UNTIL%

// Calculate FC Extrapolated

IF %ZM_ACT_PERIOD_UNTIL% + 1 = 2 THEN

DATA ([d/Date] = PREVIOUS(10, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(9, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(8, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(7, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(6, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(5, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(4, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(3, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(2, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(1, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

ELSEIF %ZM_ACT_PERIOD_UNTIL% + 1 = 3 THEN

DATA ([d/Date] = PREVIOUS(9, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(8, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(7, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(6, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(5, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(4, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(3, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(2, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

DATA ([d/Date] = PREVIOUS(1, "MONTH", [d/Version].[p/EndDate])) = RESULTLOOKUP([d/Date] = #DATE)

ELSEIF %ZM_ACT_PERIOD_UNTIL% + 1 = 4 THEN

……..

The formula repeats until month 12 is reached

ELSEIF %ZM_ACT_PERIOD_UNTIL% + 1 = 12 THEN

DATA( [d/Date] = [d/Version].[p/EndDate]) = RESULTLOOKUP([d/Date] = #DATE)

ENDIF

Does anyone have any ideas on how the formula could be made simpler?

Thank You!

Marm

[William_Yu · Accepted solution]

Hi Marm, 

   I think it can be largely simplified, by using 'until month' as parameter and two formula steps in data action. 

1. copy from actual and calculate average 

MEMBERSET [d/Measures] = "SignedData"
MEMBERSET [d/Date] = [d/Version].[p/FIRST_PERIOD] TO %Period%
MEMBERSET [d/Account] = "H111100"
MEMBERSET [d/Region] = "REG0002"

VARIABLEMEMBER #ALL OF [d/Date]

DATA() = RESULTLOOKUP([d/Version] = "public.Actual")
DATA([d/Date] = #ALL) = RESULTLOOKUP()
DATA([d/Date] = "000000" ) = RESULTLOOKUP([d/Date] = #ALL)/ MONTH(%Period%)

2. copy average to remaining periods

MEMBERSET [d/Measures] = "SignedData"
MEMBERSET [d/Date] =  NEXT(1,"MONTH",%Period%) TO [d/Version].[p/LAST_PERIOD]
MEMBERSET [d/Account] = "H111100"
MEMBERSET [d/Region] = "REG0002"

DATA() = RESULTLOOKUP([d/Date] = "000000")

Best regards, William 

[Marm · Accepted solution]

Hello William, many thanks for the quick response. I'm trying to adjust my formula.

[Marm · Accepted solution]

Hello William, many thanks for the quick response. I'm trying to adjust my formula.BR

[Marm · Accepted solution]

HI William, how is the parameter %PERIOD% defined. As a member for the dimension = Date or as a Number BR Marm

[William_Yu · Accepted solution]

As a member of dimension Date, user also select a month directly.

[Marm · Accepted solution]

Hello William, the solution works, but I cannot write data to the period '000000". The following error occurs: "Date member '000000' not found' . I have currently written the data to the period 202312. At the end, I delete the data from the period.how can I use the period '000000' in the data action?

[Marm · Accepted solution]

Hi William, I found the setting booking for period '000000". (Booking Behavior) Thank you

[CA_Priya · Accepted solution]

Hi Marm and william , can u please help me how to write data to the period '000000".

[William_Yu · Accepted solution]

Hi CA_Priya， you first need to change the book behavior of your date dimension in modeler to allow data booking on period '000000' .