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:
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:
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!