Using waterfall charts for difference analysis with Spotfire®

Introduction

Waterfall charts are powerful when you want to show the difference between for example two years, two product lines, etc., and also show what factors that contribute to the difference. Below is a waterfall chart that shows how the sales performance for a group of business units affected the total sales in 2014 vs 2015 (full bars), including the difference in performance for each business unit (flying blocks). It is important to note that the two bars on the left and right edges visualize total sales in 2014 and 2015 respectively, while the "flying blocks" in between representing the difference in sales between 2014 and 2015 for individual business units.

Note: This text is part of a series of three waterfall chart-related posts. Have a look at:

•  Why use a waterfall chart? and
• Creating a waterfall chart in Spotfire

if you have not already seen them. For more details about how to configure waterfall charts also refer to the Spotfire® help: Creating a waterfall chart

Creating a waterfall chart for difference analysis

If the data is already prepared, creating a waterfall chart for difference analysis is as simple as creating a standard waterfall chart. Here is an example of such prepared data that has the total sales for 2014, and then the difference in sales in 2015 for each business unit: See Attachment

To create a different waterfall chart just load the data from the link above into Spotfire Cloud 3.6 or later, and then click the waterfall chart icon - you are done! Add labels and formatting if you please.

That was easy enough, but in this case, the data was well prepared to create the above chart. However, if the data is transactional in its nature, some further steps will be required to create a waterfall chart for difference analysis. Load the data: see Attachment

As you see, when the data contains only individual transactions, the difference in Sales for the different Categories or the total sales in 2014 is not explicit in the data, so we need to calculate it ourselves.  We will use calculated columns that we first create and then use in the waterfall chart. Essentially, we will create one calculated column that we use, to sum up the sales of 2014, and then we will create calculated columns for Sales of each Category in 2014 and 2015.

Calculated Column for summing up all sales in 2014:

 If(Year([Date])=2014,Sum([sales]),0)

This gives a copy of the Sales column, except that only transactions for 2014 are contained in the column, sales from other years are just set to 0.

Calculated column for sales of ?Furniture? in 2014:

 If((Year([Date])=2014) And ([Category]="Furniture"),Sum([sales]),0)

This gives a column that contains only Sales transactions from 2014 for the ?Furniture? category. Other transactions are set to 0.

We will need to calculate columns in the same way for all categories, for both 2014 and 2015.

Finally

Now, we just need to use the columns we created in a waterfall chart. We will put the columns on the Y axis of the chart, and select [Column Names] on X. On the Y axis we will put the columns as follows (this can be pasted as a custom expression):

Sum([2014 Sales]) as [2014 Sales],
Sum([2015 Furniture]) - Sum([2014 Furniture]) as [Furniture Diff],
Sum([2015 Lamps]) - Sum([2014 Lamps]) as [Lamps Diff],
Sum([2015 Wallpaper]) - Sum([2014 Wallpaper]) as [Wallpaper Diff],
Sum([2015 Food]) - Sum([2014 Food]) as [Food Diff],
Sum([2015 Matts]) - Sum([2014 Matts]) as [Matts Diff]

Though it took a bit of work we now have the chart below:

Attachments