Creating Cross Calculations in a Table
In SAP Analytics Cloud, we can create cross calculations for a variety of scenarios (i.e. Rolling Forecast, Currency Conversion, etc.). Cross calculations are best used when you have two measures and you want to compare the difference between the data.
We’ve created a table to analyze and compare the variance between two time periods — Sales Revenue for the current month (CM) with our previous month (PM).
We start by selecting our table, and then navigating to the Builder panel. Under columns, we select Cross Calculations from the list.
This Cross Calculations dimension is only available for acquired data. If you’re using live HANA view, the Cross Calculations functionality is not available.
Next, we set an account filter to show only the measure we want to focus on. In this example, we’ll select Sales Revenue.
We then select ‘Add Calculation’ from our Cross Calculations column.
In the Calculation Editor, we select Restricted Measure as the Type, and choose Account Values for our Measure. This essentially takes all the account values within our table and applies this calculation to them.
We then select ‘Order Date’ as our Dimension, and for values, we choose the ‘Select by Range’ option.
We select ‘Dynamic’ for the range so that depending on when we login to SAP Analytics Cloud and view our data, it will always reflect the current month without having to manually update it each time.
To recap what we’ve done:
- We restricted all our data for our Account Values within our table. In our case, we applied a filter to only show Sales Revenue
- We then restricted that information to the current month. The reason why we selected Dynamic for the range (as opposed to Fixed) is so that whenever we visit our dashboard, it shows the current month. With Fixed, we have to manually change it to the month we want to view
- We’ve given our calculation a name such as ‘Current Month’ or ‘CM’
We can follow a similar workflow to create another restricted measure calculation, but this time we restrict the information to only display data from the previous month. As seen below, we’ve labeled it PM for Previous Month.
We can follow a similar workflow to create another restricted measure calculation. We choose the following:
- Type — Restricted Measure
- Measure — Account Values
- Dimensions — Order Date
- Name — PM (for Previous Month)
- Values or Input Controls — Previous Month
Now we have two columns — PM and CM, restricted to current and previous month and filtered to only show Sales Revenue. We can now add a variance to further aid our analysis.
Adding a variance
There are two ways we can add a variance. We can either create a calculation in the Calculation Editor (PM – CM) as seen in the figure below.
The other way is to create a front-end calculation within the table.
In either case, the result is a new column being created called ‘Delta’, which displays the previous month minus the current month.
To double check which calculation is being used, we can highlight the column and select the formula icon.
In the formula bar, we see our calculation as C1 (current month) minus B1 (previous month), which is what we want.
To add more visualization to our table, we can turn our Delta column into an in-cell chart, and then change the chart type from a bar to a variance.
As you can see, the workflow for creating a Cross Calculation in a table is relatively straightforward. With it, we compared our current month to our previous month, and showed the variance between these two values.