How to add a max aggregation calculation for previous year in a SAP Analytics Cloud Story?

[pedro_rangel]

Hi Experts,

In SAP Analytics Cloud at the story level, how can you create a variable that captures the maximum monthly value of a numeric field for the year preceding the one selected in a page filter (i.e., that captures the highest monthly value from the prior year)?

I've already created the variable to calculate the current year's maximum monthly value, but I've been unable to build the equivalent for the prior year. The requirement is to display both the current year's and the previous year's maximum monthly values simultaneously.

The story has:

• Input control for years (single selection) based on budat
• Input control for months (multiple selection) based on budat
• Analytic import model consuming from s4 onpremise via cds view

I've tried the following alternatives without success:

Alternative 1: Restricted Measure for the Prior Year based on the MAX Aggregation of the current year:

1) Create a restricted measure for the prior year based on the current year's MAX aggregation: Defined a variable to capture the maximum value of the numeric field (acdoca.hsl) using Aggregation → MAX. This didn't return the correct value for the current year—likely due to the dataset's daily granularity.

2) Workaround: Create a alculated dimension for months. Added a dimension called vMonths to represent the month of each record.

This returns all months across every year in the dataset:

3) Recreate the “current year max” variable using vMonths: Defined a new variable to get the MAX of acdoca.hsl, scoped to the vMonths dimension. This returns the expected result when the page filter selects a single year, so it correctly serves as the “current year max” value.

4) Attempt to derive the prior‑year max from the current‑year variable: Created a restricted measure atop the “current year max” variable, again scoped to vMonths and set to Select by Member → All Members. This, however, still returns the current year's maximum rather than the prior year's.

5) Test using the budat date field: Tried setting budat values via various NAVIGATION options—but all failed.

6) Test using the budat field via SELECTION: Although the variable runs, it never returns the correct prior‑year max value.

Alternative 2: Calculated Measure Using the MAX() Function.

 1) This correctly returns the sum of all monthly values from the preceding year.

2) Apply the MAX() function in a calculated measure: Use a calculated measure with the MAX() function. Instead of yielding the highest single‐month value, it still returns the full annual total for hsl.

Alternative 3: “Overall HSL MAX”

1) Define the overall‑max aggregation variable: Create a variable (hsl_max) that captures the MAX of acdoca.hsl, scoped by the budat date field.

2) Attempt to derive the prior‑year max via a restricted measure: Apply a restricted‑measure filter on hsl_max for the year before the selected one. This approach fails to return the previous year's maximum; it still yields the current selection's max value.

Any guidance would be greatly appreciated. Thank you!

[William_Yu1 · Accepted solution]

What I tried:

1. Create a model level calculated measure to read value from previous year: RESTRICT([GC_Fly] ,[d/Date]=Previous("Month" ,12 ) )
2. Create a story calculated measure with aggregation MAX based on the model calculated measure of step 1. 

This gives me  the right Previous year Max value  on current year. 

[pedro_rangel · Accepted solution]

Your suggestion didn’t work exactly for my case, but it pointed me in the right direction. I solved it as follows: 1) Create a max‐aggregation calculation in the model: Just use the hsl field with no functions applied, set the exception aggregation type to Max, specify the exception aggregation dimension as budat.calmonth. 2) Create a restricted measure in the story referencing the max‐aggregation calculation from the model, use the budat date field as the dimension, configure values for the previous year (Navigation → Previous (Shift Previous Value) → Previous Year). 3) Add the budat field to the widget: select “Year” as the level in the date hierarchy