Currently, the Waterfall chart in Einstein Analytics is only available in a horizontal format. But sometimes you will come across the need to show it in a vertical way. This becomes especially important if you want to establish some best practice rules within your company/team regarding visualizations.

One of the fundamentals is to show time only horizontally and structural information only in a vertical way.  This way your audience instantly knows which datatype is shown and it can be interpreted faster. The IBSC International Business Communication Standard is a great resource for this and I can highly recommend to check out the Standard here.

 

How to transform the Stacked Bar-Chart into a Waterfall chart?

 

The good news is, it is not as complicated but it is definitely some work to create the SAQL you need behind the scenes. As a first step, it should be considered if the effort should be taken at all or maybe another visualization can show the information in a correct way as well.

Once you decided to go for a vertical Waterfall you need to go thru the following steps.

1. SAQL: Transform your data in the correct format for the stacked bar chart (that’s definitely the biggest effort)
2. XMD: Add the colours for your new derived measures (make the bars you don’t want to see white or background colour)

 

Get your data ready

As a first step, I needed to verify my raw data to see what I need to add in the SAQL. Target here is to transform the data in such a format that I can use it in a Stacked bar chart. For my use-case I had to create a “Measure”-Dimension and use the Value as Measure.

1. Create New Column for Measure_Dimension with case statement-> Stacked bar dimension
2. Create Helper Rows for the Difference between “Running Total” and Row Value
3. Union original data with the new Helper Rows and create the final grouping.

Raw Data

As a starting point, I used some simple sales and cost measures which are conveniently already in a column structure. If they would be in a row I would simply union all different Measures so I get the following table as result.

From here I need to create “Helper” rows which I will make “transparent” in the final chart. Those “Helper” rows are the difference between each step as they are calculated as running total. So for example Sales 10,000- Discount 2,000 = 8,000 as running total. The value of the new row would be 8,000 and the row I need to add would be flagged as “A-Helper” in the “Measure_Dimension”.

Create helper rows and running total (windowing function)

To create the running total I created a “Value_Corrected” Measure which is multiplying the “Value” *-1 if it is a cost item. After that, I group again to implement the running total via a windowing function. Important is to set the “reset-group” correctly. In my example, my focus is on ‘Sales-Reps’. The second crucial part is the order within the function. If this order is not correct my results would be useless.

 sum(sum('Value_Corrected')) over ([..0] partition by 'Sales_Rep' order by ('SortOrder','Measure'))

Union Original data rows and “Helper”-rows

As the last step, I unioned both streams so I have my Stacked-Bar dimension “Measure_Dimension” ready to use. I just need to make sure I created my final grouping so the chart can interpret the data correctly.

SAQL Code

--////1. create 'Measure Dimension' to flag the original rows used in the visualization (color: "Z-Start" = grey ,"Z-Negative" = red, "Z-Positive" = blue , "Z-End" = grey)

q = load "Waterfall_data";
q = filter q by 'Value_Dimension'!="A-Helper";
result = group q by ('Sales_Rep','SortOrder', 'Measure');
result = foreach result generate q.'SortOrder' as 'SortOrder', q.'Measure' as 'Measure', case when q.'Measure'== "Sales" then "Z-Start" when q.'Measure' in ["Discount","Material Cost","Sales Cost","Overhead"] then "Z-Negative" when q.'Measure' in ["Profit"] then "Z-End" when 'Measure' in ["Affiliate - Revenue"] then "Z-Positive" end as 'Measure_Dimension', sum(q.'Value') as 'Value', q.'Sales_Rep' as 'Sales_Rep';

--/////// 2. creating additional rows for the stacked dimension "Measure_Dimension" which will be white/invisible in the final Waterfall chart.

--2.1 Create stacked dimension "Measure_Dimension"
result_helper = group q by ('Sales_Rep','SortOrder', 'Measure');
result_helper = foreach result_helper generate 'SortOrder' as 'SortOrder', 'Measure' as 'Measure', case when 'Measure'== "Sales" then "Z-Start" when 'Measure' in ["Discount","Material Cost","Sales Cost","Overhead"] then "Z-Negative" when 'Measure' in ["Profit"] then "Z-End" when 'Measure' in ["Affiliate - Revenue"] then "Z-Positive" end as 'Measure_Dimension', sum(q.'Value') as 'Value','Sales_Rep' as 'Sales_Rep';
result_helper = group result_helper by ('Sales_Rep','SortOrder', 'Measure', 'Measure_Dimension');
--2.2 preparing values for running total (partition in the next step)
result_helper = foreach result_helper generate 'SortOrder' as 'SortOrder', 'Measure' as 'Measure',case when 'Measure_Dimension'=="Z-Negative" then sum('Value')*-1 when 'Measure_Dimension'=="Z-Positive" then 0 else sum('Value') end as 'Value_Corrected' ,'Sales_Rep' as 'Sales_Rep', 'Measure_Dimension' as 'Measure_Dimension';
result_helper = group result_helper by ('Sales_Rep','SortOrder', 'Measure', 'Measure_Dimension');
--2.3. calculation running total 
result_helper = foreach result_helper generate 'SortOrder', 'Measure',case when 'Measure_Dimension' not in [ "Z-Start" , "Z-End"] then "A-Helper" else 'Measure_Dimension' end as 'Measure_Dimension', sum(sum('Value_Corrected')) over ([..0] partition by 'Sales_Rep' order by ('SortOrder','Measure')) as 'Value','Sales_Rep' as 'Sales_Rep';
-- deleting rows we don't need in the final union
result_helper = filter result_helper by 'Measure_Dimension' not in [ "Z-Start" , "Z-End"];

---/////3. create union to combine original rows and helper rows
append = union result, result_helper;

final = group append by ('Sales_Rep','SortOrder', 'Measure', 'Measure_Dimension');
final = foreach final generate 'Sales_Rep' as 'Sales_Rep', 'SortOrder' as 'SortOrder', 'Measure' as 'Measure','Measure_Dimension' as 'Measure_Dimension', sum('Value') as 'Value';

Customize Stacked Bar

When the SAQL is finished I simply toss it into the Stacked Bar chart and select “Measure-Dimension” as a stacked dimension. The chart uses the default colours which I need to change in the next step. Unfortunately, the Conditional Formatting feature doesn’t help us here as it doesn’t work for Derived Dimensions. It shows in the Conditional Formatting section but we can’t pick values to edit them.

 

 

Upload modified XMD for Dataset

To change the member colour of my derived Dimension “Measure_Dimension” I need to modify the XMD. To do this I downloaded the existent XMD file from my dataset and added a new field “Measure_Dimension” and created a member/color definition as seen below. (Please make sure to keep the downloaded version so you can roll back the setup if needed)

{
"dataset": {},
"dates": [],
"derivedDimensions": [
{
"conditionalFormatting": {},
"customActions": [],
"field": "Measure_Dimension",
"members": [
{
"color": "#B0ADAB",
"member": "Z-End"
},
{
"color": "#FF9897",
"member": "Z-Negative"
},
{
"color": "#98B1FF",
"member": "Z-Positive"
},
{
"color": "#DDDBDA",
"member": "Z-Start"
},
{
"color": "#FFFFFF",
"member": "A-Helper"
}
],
"recordDisplayFields": [],
"salesforceActions": [],
"showInExplorer": true
}
],
...

I uploaded the modified XMD to the dataset again and my “Waterfall”-Stacked table is showing now the correct colours.

Benefits of Stacked  “Waterfall-Bar”

The stacked vertical Bar chart offers a lot of flexibility. Not only can you create a Waterfall chart vertically, you can extend the functionality for e.g. by integrating Subtotals which you can’t do in the default “Waterfall” chart.  With workarounds like this, the use cases Einstein Analytics can cover are growing dramatically and adds tremendous value for the users.