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:

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)`

Once you have added the expressions, click on Finish.
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)
```
Repeat for the Expenses $ expression. Enter the following expression for the Bar Offset:
```
Sum({<Category={'Sales'}>} Value)
-Sum({<Category={'COGS', 'Expenses'}>} Value)
```
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)
```
Click on OK to save the changes.
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.

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

QlikView for Developers Cookbook

By : Stephen Redmond

QlikView for Developers Cookbook

By: Stephen Redmond

Overview of this book

Creating a waterfall chart

Getting ready

How to do it…

How it works…

There's more…

QlikView for Developers Cookbook

By : Stephen Redmond

QlikView for Developers Cookbook

By: Stephen Redmond

Overview of this book

Creating a waterfall chart

Getting ready

How to do it…

How it works…

There's more…

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access