SAP Analytics Cloud for planning - Dynamically sum up the last 12 months

[guenay]

Hi friends,

I want to sum up the last 12 months. E.g. Jan-Dez 23 to Jan 24 and divide it by 12. 

With Aggregate and write to in the advanced formulas, this works fine, when the dates are fixed. 

However, I'm seeking a way to make this more flexible based on the current month. For instance, since it's now March 24, I want to sum up the period from March 23 to February 24.

Any suggestions would be greatly appreciated.

Kind regards,

Ismail 

[N1kh1l · Accepted solution]

@guenay 

Keep your aggregate and write to as it is but change your memberset as below

MEMBERSET [d/ZACCOUNT_TEST]="SALARY"
MEMBERSET [d/Date]= PREVIOUS(12,"MONTH",TODAY()) TO PREVIOUS(1,"MONTH",TODAY())

DATA([d/ZACCOUNT_TEST]="SALARYPREV12",[d/Date]="202401")=RESULTLOOKUP()

I am just writing the sum to 202401. This will be dynamic based on current date and hence current month.

 

Br.

Nikhil

[EmanuelGraves]

To achieve the flexibility you're seeking in your calculation, where you want to dynamically sum up the last 12 months from the current month, you can use date functions and relative references. 1.Start by obtaining the current month and year. You can use date functions available in your tool or programming language to do this. 2.Subtract 12 months from the current month to determine the starting month of the 12-month period. 3.Use the current month as the end date of your period. 4.Sum up the values within the specified date range. Start_Date = Current_Date - 12 Months End_Date = Current_Date Sum(Values) Where Date >= Start_Date and Date <= End_Date SELECT SUM(Value) FROM Table WHERE Date >= DATEADD(MONTH, -12, CURRENT_DATE) AND Date <= CURRENT_DATE