SAP Analytics Cloud for planning - Aggregate Values on a dynamic Date

[guenay]

Hi everyone,

I’m looking for a way to aggregate values based on a dynamic date. For example:

Values from 202501 to 202503 should be aggregated to a date that’s twelve months ahead of the current date, resulting in something like:

202501–202503 → 202604

Has anyone tackled something similar or have suggestions on how to approach this?

Looking forward to your thoughts!

BR,

Ismail 

[MoonJun]

Hi @guenay 

Using the Data action "Advanced formulas", you can get your expected result based on the following.  

MEMBERSET [d/Date] = %From_Date% TO %To_Date%

DATA([d/Date]=NEXT(13,"MONTH",%To_Date%)) = RESULTLOOKUP()

The script is working with 2 external parameters "%From_Date% and %To_Date%. Please refer below screen shot for the Parameters.

I hope this is helpful to you, and if I have misunderstood anything, please feel free to reach out.

Moonjun

[guenay]

Hi Moonjun, Thanks for the quick reply. In your solution, I see that it requires entering two date parameters. My goal is to avoid using any input at all—would there be a way to achieve that?

[MoonJun]

Hi @guenay

I am not clear about the "dynamic date" you mentioned. How can we know the dates "202501" to "202503"? Is it a fixed date or the previous 3 months from the current month(today: 16-Apr)? 

One more case is below,

202501–202503 → 202604

202502–202504 → 202605

...

202511–202601 → 202702

202512–202602 → 202703

Regards,

Moonjun