Don’t you wish Qlik Sense bar charts had the Bar Offset feature, similarly to QlikView bar charts? We surely do. How else can you develop a waterfall chart? Well, then if you think about it long enough, maybe it’s not entirely impossible…

The following explanation provides detailed steps to developing your own waterfall chart in Qlik Sense. For those of you who can’t stand reading long texts, I can give out the recipe now.

If you know what we are talking about, you can stop reading and start playing. For the rest of us, here is the detailed explanation.

As any “work around” solution for a missing feature, this one will take a bit of extra preparation. For starters, the data for the waterfall chart needs to be prepared in a special way. In the following snippet of code, I loaded a few values for my sample app:

In your own applications, the amounts don’t necessarily have to be pre-summarized, of course, however some extra tinkering will be necessary:

• The Profit numbers need to be pre-calculated (or calculated in a special way in the chart expression)
• Each “cost bucket” , or Stage, needs to be qualified with two extra attributes: Multiplier and Offset.
• Multiplier defines if the amount should be positive or negative. All revenues are positive and all costs are negative.
• Offset defines whether or not the bar for this value needs to start from a non-zero offset. Revenue and Profit start from zero, while the other Stages require the offset.

These settings describe your data for the calculations in the waterfall chart. In addition, we can create a dummy dimension that we will use to form a stacked bar chart. We could use the ValueList() function instead, however using a field is much easier. The field has two possible values – Offset and Value.

Now the data is ready to be used in the chart. If you are following along, save your script and load the data.

The next step is to create a stacked Bar Chart with two dimensions – Stage and Dim. Nothing unusual here.

Next, we need to create the Measure, and this is where most of the trick is hiding. Let’s describe the desired logic verbally before spelling out the formula. For the “values” (Dim=’Value’), we simply aggregate the amounts. For the “offset” entries, we should calculate the range sum of all previous amounts, with their corresponding Multipliers, but only for those Stages that are marked with Offset=1.

If the current bucket is “negative”, then the offset needs to be adjusted for the the volume of the current amount. In other words, if the current amount is 10 (marked as “negative”) and the accumulated offset is 100, the bar needs to start at 90 in order to end at 100.

Positive buckets, on the other hand, do not need any such adjustment, – the positive bar +10 would simply begin at 100.

Here is the same logic, formulated as a Qlik Sense Measure:

if(Dim = 'Value', 
 sum(Amount), 
 if(RowNo(total)=1 or Offset=0, 
 0, 
 RangeSum(Above(total sum(Amount*Multiplier)/2, 1, RowNo(total))) + 
 RangeMin(0, Sum(Amount*Multiplier))
 )
)

This formula deserves a bit of explanation:

• As we already said, when Dim=’Value’, then the formula simply returns the sum of Amount. That’s easy.
• Otherwise, the offset value is assigned to 0 for the first row in the chart or for any Stages with Offset = 0.
• The Above() function returns an array of all previous values, starting from 1 (the previous row) and counting the number of values that is equal to the current row number.
• Notice that all the Amount values are multiplied by the corresponding Multiplier, to account for positive and negative entries. Also, the sum is divided by 2, and here is why. The Above() function duplicates all the results because of the secondary (dummy) dimension. We have to divide by two to compensate for the duplication.
• The RangeSum() function is used to sum up all the elements of the array returned by the Above() function.
• Lastly, we add the current value sum(Amount*Multiplier) to subtract the current amount for the negative entries. The RangeMin() function is used to account for negative values only – any values that are higher than zero will get replaced by zero.

If you are following our tutorial, you should already see a stacked bar chart, painted in two colors. The only remaining part is to take care of the colors.

Open Properties > Appearance > Colors and legend and define custom color By Expression. There are many ways to color the chart. We picked something simple, just to get the message across. Feel free to experiment on your own. The following formula makes the color fully transparent for the offset entries, using the ARGB() function with Alpha = 0. For the actual values, we painted all the positive values in green and all the negative ones in red:

if(Dim = 'Offset', 
   ARGB(0,255,255,255),
   if(Multiplier >0, Green(), red())
)

Voila! Your waterfall chart is ready to make your accounting department happy.

If you enjoy the level of depth in this blog, please check out our new book QlikView Your Business, where we describe the business and the technical aspects of developing basic, intermediate, and advanced QlikView analytical applications.

For advanced Qlik developers, we also recommend to visit Masters Summit for QlikView – the only advanced technical forum available today for experienced Qlik developers. The sidebar on the right contains links to both the book and the Summit.

Enjoy, and don’t forget to leave your feedback! See you around the Community!