Introduction

Waterfall chart is a variation of bar chart using properties from expression to move each bar to represent a piece of total. As you can see at the picture, is possible to show to decision maker how sales prices are mounted. I mean, from total sales some money need to pay employees, taxes, and other obligations. Generally, the first bar will show the total of any other bars. In our case, the first bar is total of sales. Other values are shown based on distribution of total sales. This will helping the decision maker to see what’s the most important value that is part of total sales. Ok, let’s go to prepare some data as sample to create this chart. Instead of create a .QVW to download, this post will teach you how to create yours.

If you’re new in QlikView, start the the Desktop version from Windows operating system and from File menu click New. Usually QlikView will show a kind of wizard to help you load data. But we will not use it for now. So, just cancel the wizard if it is shown. All data loaded to QlikView is done from editor script. But you don’t need to write complex code. For now, just create a simple table using the instructions below. To do that, open the script editor pressing CTRL + E. Copy the following rows using transference area (CTRL + C) and paste below the default rows presented on script. If is necessary, press ENTER sometimes to create empty rows. One way to create a table using QlikView is using a LOAD INLINEstatement. Let’s go there.

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

After paste the code sample, just save your job and press CTRL + R to load this data to the memory. When QlikView finish this task, it will take you to main window.

Creating the Chart

To create the chart above just click on any empty space of main window using right button and select New Sheet Object. From submenu click Chart. Default chart in QlikView is a bar chart. Just click Next do select the Dimension. In our case, dimension is category field. Add it to dimension used side and advance to next stage. Now is time to enter a formula using the Expression window. The first bar is blue and represent total sales. So, enter the following formula:

=sum({<Category={'Sales'}>} Value)

Note: To create this chart is necessary use SetAnalysis syntax. To learn about it, see this post.

If the chart creation is finished at this time is possible see one blue bar. Now, what you need to do is create new expressions (formulas) for each values that is part of total sales. Take a look at the picture at side where a new expression were added. Notice that all expression need to be filled with a Label, because it will be used inside expression properties. That’s why the Label is filled with COGS. Don’t forget to do the same procedure with Sales, the previous expression. That’s mean you need to add a label like Sales. Now you should repeat the procedure for each new expression, using the following list:

• If you added COGS, go ahead to next step. Otherwise, add a new expression using =sum({<Category={'COGS'} >} Value) as formula.
• For COGS expression, remember to change label field to COGS text, otherwise waterfall will not work.
• Add a new expression using =sum({<Category={'Expenses'}>} Value) as formula. Change the label to Expenses.
• Repeat the process using =sum({<Category={'Tax'}>} Value) as formula of Tax. Don’t forget to change label to Tax.

The importance of labels is the fact you can use them to calculate other expressions. For example, to calculate the Net Value is necessary get the price of sales without any taxes, expenses and COGS (other costs). So, using the labels previously defined add a new expression using the following formula:

=Sales - Tax - COGS - Expenses

Of course it will work if you change each label as expected. Using each label QlikView can calculate the formula without enter the complete expression again. Up this moment the dashboard has a chart with bars in different colors, but is necessary to tell to QlikView move each one to expected position. To do that, go back to the Chart properties and expand the plus signal at side of COGS expression. Take a look at the Bar Offiset property, and click over on it. Now is possible configure its property to move the bar using the following formula on Definition field: Sales - COGS.

Using this procedure, repeat it for each expression as the following steps:

• For Expenses expression, define BarOffset as Sales - COGS - Expenses.
• For Tax, define BarOffset as =Sales - COGS - Expenses - Tax.

Nice, close the chart properties. Your chart is similar the picture shown at the top of this post. Other possible changes you maybe wanna to do is about visual refinements like increase space between the legend, change legend position and add numbers on top each bar. Back to the properties to change some chart aspects.

• Select Values on Data Points for each expression from Expression tab.
• Add more one expression using the following formula:

='Sales: ' & num(Sales, '$(MoneyFormat)') & Chr(10) & 
 'COGS: ' & num(COGS, '$(MoneyFormat)') & Chr(10)

• Change label for the new expression to Tooltip.
• Click on Tooltip expression and uncheck Bar option and select Text as Pop-up
• Go to Presentation tab and click on Settings button. Change Line Spacing<code> to <code>Medium.
• Go to Number tab and configure each expression with a number format, as you preference.

Done!