How to pass multiple entries to calculate measures in SAC?

SAP Analytics Cloud has the capability to make complex calculations based on many input control entries. In this article I’d like to highlight this feature.

First of all, let’s have a look at the following scenario:

Supposing we have the following data set:

We want to get the difference between 2018 and 2017 as both an absolute value and a percentage. However, we want to display this KPI for Revenue or Volume. The user can decide what combination to see making a selection in 2 input controls:

First Input Control will contain the following values: ‘Revenue’ or ‘Volume’

Second Input Control will contain the following values: ‘Absolute value’ or ‘Percentage’

Step 1: Create the model with the previous data

In this case, the model has been created on a flat file.

 

Step 2: Create a second model for the data used in Input Controls

We will create a model based on a flat file with this data:

Based on these columns, we will create the Calculation Input Controls later in the story. Notice that it is not necessary to create a relation between these columns. They will be used as standalone lists in the Input Control. As we only have dimensions, while creating the model, SAC will automatically generate a measure for us.

Step 3: Create a Story on top of the 2 models

Make sure both models are added to the story:

Step 4: Add a bar chart

Step 5: Add the dimension Country to the graph

Step 6: Add a calculated measure

 

Step 7: Create the Calculation Input Controls

Firstly, we will create the Calculation Input Controls for the type of measure: Revenue or Volume. In the calculation editor, click on Create New under Input Controls.

Give it a name, make sure the option Existing Dimension is selected, select the model and dimension that contain the corresponding data and choose the values Revenue or Volume. Set the selection for the user to Single Selection.

 

Repeat the same steps in order to create the Input Control with the values ‘Absolute value’ and ‘Percentage’.

Step 8: Edit the formula based on Calculation Input Controls

Once the Input Controls have been created, now we can edit the formula using their selection response. While editing the formula, we can click on the input controls names that appear on the right in the calculation editor. Notice that with the annotation ‘@’ the input control returns the selection result.

The formula is the following:

IF([@Measure]='Revenue' and [@Abs or Perc]='Absolute Value';  [data_country:Revenue_2018]-[data_country:Revenue_2017];

  IF([@Measure]='Revenue' and [@Abs or Perc]='Percentage';  ([data_country:Revenue_2018]-[data_country:Revenue_2017])/[data_country:Revenue_2017]*100;

IF([@Measure]='Volume' and [@Abs or Perc]='Absolute Value'; [data_country:Volume_2018]-[data_country:Volume_2017];

IF([@Measure]='Volume' and [@Abs or Perc]='Percentage'; ([data_country:Volume_2018]-[data_country:Volume_2017])/[data_country:Volume_2017]*100 ))))

On the canvas, the input controls are indicated by the formula icon. If you hover over the icon, all calculations associated with the input control are displayed. By default, the input controls are displayed in token mode where input values can be selected from a drop-down list. The input controls can be expanded into widget mode, where radio buttons appear beside each value.

Now you can change the input control selection and notice how the KPI changes.

Note: The Calculation Input Controls based on Existing Dimension requires import data connectivity.

I hope you will take advantage of this feature and make your calculations dynamic. Give your users the flexibility to choose what they want to see at a glance.