Creating a waterfall chart

A waterfall chart is a type of bar chart used to show a whole value and the breakdown of that value into other subvalues, all in one chart. We can implement it in QlikView using the Bar Offset option.

In this example, we are going to demonstrate the chart showing a profit and loss breakdown.

Getting ready

Load the following script:

LOAD * INLINE [
    Category, Value
    Sales, 62000
    COGS, 25000
    Expenses, 27000
    Tax, 3000
];

How to do it…

The following steps show you how to create a waterfall chart:

1. Create a new bar chart. There is no dimension in this chart. We need to add three expressions:

   Sales $	Sum({<Category={'Sales'}>} Value)
   COGS $	Sum({<Category={'COGS'}>} Value)
   Expenses $	Sum({<Category={'Expenses'}>} Value)
   Tax $	Sum({<Category={'Tax'}>} Value)
   Net Profit $	Sum({<Category={'Sales'}>} Value) -Sum({<Category={'COGS','Expenses','Tax'}>} Value)

2. Once you have added the expressions, click on Finish.

   

3. Edit the properties of the chart. On the Expressions tab, click on the + sign beside the COGS $ expression. Click on the Bar Offset option. Enter the following expression into the Definition box:

   Sum({<Category={'Sales'}>} Value)
   -Sum({<Category={'COGS'}>} Value)

4. Repeat for the Expenses $ expression. Enter the following expression for the Bar Offset:

   Sum({<Category={'Sales'}>} Value)
   -Sum({<Category={'COGS', 'Expenses'}>} Value)

5. Repeat once more for the Tax $ expression. Enter the following expression for the bar offset:

   Sum({<Category={'Sales'}>} Value)
   -Sum({<Category={'COGS', 'Expenses', 'Tax'}>} Value)

6. Click on OK to save the changes.

7. The waterfall chart should look like the following screenshot:

   

How it works…

The Bar Offset option for bar charts allows us to calculate the start position for each of the bars, other than the 0 default value.

We use a Set Analysis expression to easily calculate the values.

There's more…

While this example is reasonably trivial, it is reflective of a real-life example and there are many others. Using Set Analysis functions to calculate the value for the offset is very typical.