How to create Waterfall charts in QlikView

Every now and then customers want waterfall charts in our QlikView applications. As this is not a standard chart type in QlikView, it is not completely straightforward. This is how we have done it.

The Source Data

In this example, we will use this oversimplified source data.

It is as easy to use data with different years on different rows. However, in order to keep the expressions as readable as possible, we use this really simple data.

The Waterfall Definition

In this example, the waterfall dimensions are defined in Excel, in order to make it easier to modify them, even for users without any QlikView knowledge. A part of the Excel file looks like this:

The fields in this sheet are:

• BU
  • Defines the Business Units to be shown in the graph. In other words, this is the part that tells which data to show for each bar.
• Waterfall1
  • Used as a Dimension in the graph. The text in this field is shown as the dimension below the bars in the graph.
• OrderBy
  • The order in which the columns are shown in the graph.
• WaterfallFormatting
  • The color of the bar. RedGreen is used for bars that are red if the numbers are negative and green if the bars are positive. Other formatting can be applied here too, as long as it is defined in the graph in QlikView.
• OffsetRowsBack
  • The sum of the previous x rows are used as offset to get the starting point for the current bar. If this is 0, the bar will start from 0.
• ActMultiplier
  • The actual value of the account is multiplied by this value. In this example, the Act value is not taken into account in the “Plan” bar, as it is 0.
• PrevMultiplier
  • The Previous year’s value of the BU is multiplied by this value. In this example, the Previous year’s value is not taken into account in the “Act” bar, as it is 0. As we want to show the difference between Actual and Previous values, most PrevMultipliers here are -1, making the bar value Actual – Previous Year.

As you can see, every waterfall has a distinct column for defining the bars in the waterfall. If a new waterfall is added, just one new column needs to be added in this sheet.

The Waterfall in QlikView

In QlikView, the graph is created as a bar chart with offsets to make it a waterfall diagram.

Dimensions

On the dimensions tab, we use Waterfall1 as the dimension, as defined in the Excel file. As we may have multiple waterfalls defined in the same table, it is important that we check Suppress When Value Is Null. This is because we want to show the values for just this waterfall.

Expressions

On the Expression tab, there is just one expression, which looks like this:

Sum(CurrentYear)*ActMultiplier
+
Sum(PrevYear)*PrevMultiplier

This sums together the Actual value (considering the ActMultiplier), the Previous Year’s value (considering the PrevMultiplier).

Offset

The bar offset is defined as:

rangesum (above(Column(1),1,only(OffsetRowsBack)))

This sums the previous columns together, in order to define the starting point for the current column. In other words, this is the part that makes the chart a waterfall chart.

Coloring

The background color of the bar is defined as:

if(only(WaterfallFormatting)='Base',
    Blue(),
    if(column(1) <0,Red(),Green())
)

Which makes the bar the selected color.

In this case, the final waterfall graph will look like this:

The QlikView example file can be found here and the source data can be found here.