SAC Data Action Advanced Formula - Cacluate Average of Past Periods

[former_member138894]

Hello Experts,

Can you please help with the below request

My Requirement:

I have to take values from a range of past periods that are defined by the user in runtime (as Input Parameters), then calculate the average of these values and post the result average amount to the next 42 periods (starting from current period).

For Example:

Past Periods Range: Jan.2022 to May.2022

Future Periods: April.2023 to October.2026

In Advanced Formula, I have defined the scope of the future periods as:

MEMBERSET [d/Date] = PERIOD(TODAY()) TO NEXT(42,"MONTH", TODAY())

How to Calculate the Average value from the past periods (as they are not part of the memberset scope).

I have tried using FOREACH, Variables, etc.but unable to achieve this as Resultlookup doesn't accept Range.

Thanks for your help.

Regards

Malya

[N1kh1l]

malya_7

Your Date scope should be on periods for which Average has to be calculated. See an example below.

Parameter:

Advanced Formula: Adjust as per your model dimension names and measures

MEMBERSET [d/MEASURE] = "QUANTITY"
MEMBERSET [d/CALMONTH]=%Average_Period%  // Parameter for Average periods
MEMBERSET [d/SAP_CEP_PRODUCT] = "A"
VARIABLEMEMBER #TOTAL OF [d/CALMONTH]
VARIABLEMEMBER #NUM OF [d/CALMONTH]

FLOAT @AVG
INTEGER @COUNT
@AVG=0.0

DATA([d/CALMONTH] = #TOTAL) = RESULTLOOKUP()
DATA([d/CALMONTH] = #NUM) =  RESULTLOOKUP() * 0 + 1
@AVG = RESULTLOOKUP([d/CALMONTH] = #TOTAL) /  RESULTLOOKUP( [d/CALMONTH] = #NUM)

FOR @COUNT = 0 TO 41 // adjust this for your future periods
DATA([d/CALMONTH]=NEXT(@COUNT,"MONTH",TODAY()))=@AVG
ENDFOR
 

Data Action Run:

Output: I ran for only 8 periods i.e. @COUNT = 0 TO 8. You can see the average was 250/5 = 50

Hope this helps !!

Please upvote/accept if this helps

Nikhil

[N1kh1l]

malya_7

If you find the answer helpful, please close the thread by accepting the answer.

Nikhil