SAP Analytics Cloud Planning : some examples of Waterfall Chart configurations
This blog aims to illustrate Waterfall Chart configurations based on different model configurations.
Depending on your configuration options (measures or account-type model, dimensions analysis, etc.) the retrieval of your data can dramatically change in the Waterfall Chart.
- If you have no Account dimension, you can use multiple measures in the Waterfall Chart.
- If you have an Account dimension, you must choose a single measure and at least one account.
- If you need to display multiple measures in a Waterfall Chart on a Model with an Account-type dimension, you can work around the issue leveraging the MEASURELOOKUP (available since Q4/2022) or as an alternative, you can use Data Actions to put the data required for the Waterfall chart in an adapted structure.
In some more complex cases, it might be worth considering leveraging a dedicated model.
If the waterfall retrieval of your data is key to your business user, then please include the waterfall requirements at the beginning of your project, if you didn’t, you can always try our alternatives😊
All our examples and workarounds are based on the new model.
This blog has been created with the great help and knowledge of my colleague Christian Schoenbein, from the Analytics Competence Center, T&I HANA Database and Analytics.
Example 1 – Generic dimension hierarchy
This example is based on a model with a generic measure (like Amount), comparing 2 different versions (like Actual and Budget) based on a generic dimension.
The Waterfall Chart shows the variance between both versions broken down on the generic dimension’s hierarchy.
Please note that you need to activate the Variance Waterfall property.
Configuration of the Waterfall chart
Example 2 – Measures with formulas
This example is based on a model with only several measures and a calculation summing all measures.
Calculation based on Measures
The waterfall shows the impacts of the different measures of the calculated measure between two different versions (Actual and Budget ).
Configuration of the Waterfall Chart
Please note that the detail of the calculation formula cannot be expanded in the waterfall chart, this feature is only available for hierarchies of a dimension.
Example 3 – Account dimension hierarchy
In this example, the Waterfall Chart displays the variances between both versions (Actual and Budget) broken down on the Account dimension hierarchy.
You can select and order the leaves or the nodes of the Account dimension you want to display in the waterfall chart including the Variance Waterfall option to show the difference between the versions.
In this configuration, you can also use formulas on the Account dimension.
The order in the chart depends on the configuration selection.
Configuration of the Waterfall Chart
Example 4 – Workaround for a Model with Account and several Measures
As mentioned above, you cannot create a Waterfall Chart based on multiple measures if you your model contains an Account-type dimension.
This use case demonstrates how to create a Waterfall Chart based on several measures and to work around the single measure selection in a Waterfall Chart.
The solution follows these steps:
1. Create a technical dimension,
2. In this dimension, associate to each measure a dimension member, don’t hesitate to create a complementary hierarchy if it’s useful to provide drill options to end users,
3. Create a technical measure to store the data,
4. Create a data action in the story to allocate the data of the measures to the technical measure and the dimension members,
5. Create a Waterfall Chart based on those technical measure and dimension members.
Creation of the technical dimension
Creation of the measure
Data actions configuration
Example of the configuration of one allocation of the data measure to the technical dimension member.
Send the data to the starting and ending points of the Waterfall Chart.
This latest data action could be enhanced with parameters to allow the end user to select the month of the “Ending point” when running the data action.
Waterfall Chart with expanded hierarchies
Waterfall Chart configuration