Create dynamic variance between versions on table in SAP Analytics Cloud

[Chandni]

Introduction

Its always smart to be well informed on how your planned financial data looks against your current stats. But have you ever wondered, if you could have also compared your multiple forecasts versions, only to get you more clarity on your future business decisions, with minimal effort?

Sounds nice.. isn't it.

With SAP Analytics Cloud(SAC), version management and calculation Input controls , it is possible to create dynamic variance between multiple versions on a table.

Scenario

In this blog, we will create dynamic variance on multiple versions within a single table in SAC.

Here are simple steps to do so:

1. Create Story with a table.


2. Create restricted measures having version dimension as selecting criteria.


3. selecting members from version Calculation Input Control


4. Create table client calculation to compare two selected versions.

Needless to mention some pre-requisite:

1. SAP Analytics Cloud user with planning roles


2. Planning Model with multiple forecast/planning versions to compare with Actuals


3. Basic understanding on SAC version management, calculations and table functions

Lets get started..

1. Create a Story with a table

You can create either a classic or unified Story. Add table. Choose a planning model which you would like to create this dynamic variance on. Make sure model has multiple versions to play around. Add required dimensions(including versions) , accounts and measure.

Here you can see, I have 2 forecast versions.

Table with multiple forecast versions

2. Create Restricted Measures and 3. Version Calculation input control

We will now create restricted measures to differentiate between versions type. In my case , I have two categories, Forecast and Actual. I will be creating two restricted measures by adding new Calculation from table builder panel. You can modify this based on your requirement like published or unpublished versions etc..

Add calculation

 

Forecast Restricted Measure

 

In Values for Version Dimension, create a calculation input control. Select values for that input control. in my case, I have selected both forecast versions

Forecast Calculation Input Control

 

Similarly, I will create Restricted measure and Calculation Input Control for Actual version. though, in my scenario it is not required as I have only one Actual version, but in case you have multiple published versions to compare with , I request you to consider creating it for more clarity.

Actual Restricted Measure

 

And corresponding Calculation Input Control for that

Actual Calculation Input Control

Once you have completed this step, please remove previously selected measures from the table via filtering and select only two created restricted measures . Your table and story will look like

Table with RM and CIC

 

4. Create table client calculation to compare two selected versions.

In this last step, we will create table client calculation to compare 2 selected version. This can be done by selecting both version columns.

Table client calculation

A new column will be added which will show difference between the two version columns. I have renamed it as Variance

Final result

Well done!!

You have successfully create dynamic variance with versions on Table. To see this in action, you can change the versions from calculation input control to notice Variance column dynamically changes.

Dynamic Variance between versions on Table

 

Bonus Tip

If you have currency data and your model is Currency conversion enabled. You can get the variance in the recepective currencies. Only to be sure of using the currency conversion measure or base measure with currency enable. To learn more about currency conversion please refer Plan with Currency Conversion

Dynamic Variance with Currency Conversion

 

Summary

In this blog, we have learnt to create dynamic variance with help on version, calculations and tables in SAP Analytics Cloud. Additionally, we can have currency conversion on variance.
If you want to learn more about input controls, you can refer Input Controls

 

Thanks for the read! Hope you find this blog helpful. Please share your thoughts and questions via comments. I request you to follow for future interesting blogs on SAP Analytics Cloud

[souleymane]

Hi Sinha,

Great blog.
We can embellish the table by adding a graph on the variance column.

One question: can we do cell merging on SAP Analytics Cloud?

Regards,

[Chandni]

Hi Sam,

Thank you! Yes, we can add in cell chart for variance make it more appealing.

To answer your question, as of now SAC only supports merging on Custom cells. Cells with backend data aren't allowed to be merged. There's an influence request on merging table header cell, which I thought you would like to see and vote.  https://influence.sap.com/sap/ino/#/idea/233412/?section=sectionDetails.

Best Regards,

Chandni