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!
Why don’t you just use the Bar Offset property in the expression properties?
That way you don’t have to use 2 dimensions.
Țhank you for your comment. You must be thinking about QlikView. Unfortunately, the same attributes are not supported in Qlik Sense
ждем книгу 🙂
This is very good, however i am struggling with different scenario. Lets assume we have following set of records:
LOAD * INLINE [
Stage, Amount, Multiplier, Offset
Revenue, 100, 1, 0,
Labor, 200, -1, 1
Material, 15, -1, 1
Overhead, 10, -1, 1
Profit, -135, 1, 0
How do we deal with “Labor” bar on waterfall which has to go from Offset=100 and go across “0” value and finish on “-100”
Any ideas how to solve this?
thank you for your message. Your case is a bit trickier than usual. Not sure if this “trick” can be used in the case when a single bar needs to cross from the negative territory to the positive territory.
I’d recommend experimenting with the position of the X-axis. If you shift the X-access down into the negative territory (by adding the same calculated amount to all Measures), all your bars will technically appear as positive. Then, you could present the “true” X-axis in the form of a reference line. I know this is quite tricky, and you’ll have to find a creative way of showing one number while plotting another number (this might be possible using the dual() function). However, it might help you trick the chart into showing both positive and negative numbers.
I would love to see the solution, if you manage to build one.
Did you find a way to manage. this situation?
I have the problem that you have presented…
Thanks in advance…
Can this be done with a date dimension?
To show for example the duration of projects on a timeline
thank you for your question, it’s an interesting one (if you ever listened to one of my lectures, you know what I mean by that…) I can’t see why not, theoretically, even though I’d probably prefer a Gantt chart as a better visualization for that purpose… There is no Gantt chart out of the box in Sense, but I’ve seen some nice extensions for that.
I can’t reach the output as is in this page… Could you share the App to see where I’m wrong?
Thanks in advance…
Thank you for your interest… I’d love to share the source, but apparently I didn’t save the original App. It was a while ago, and I lost it somehow…
Sorry about that!