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.

You need to develop a Stacked Bar chart with two dimensions, and make the color transparent for one of the dimensions.

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:

PL:

LOAD * INLINE [

Stage, Amount, Multiplier, Offset

Revenue, 100000, 1, 0,

Labor, 20000, -1, 1

Material, 15000, -1, 1

Overhead, 10000, -1, 1

Profit, 55000, 1, 0

];

LOAD * INLINE [

Stage, Amount, Multiplier, Offset

Revenue, 100000, 1, 0,

Labor, 20000, -1, 1

Material, 15000, -1, 1

Overhead, 10000, -1, 1

Profit, 55000, 1, 0

];

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.

DummyDimension:

LOAD * INLINE [

Dim

Offset

Value

];

LOAD * INLINE [

Dim

Offset

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 A
*bove()*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!

December 17, 2018

Social media & sharing icons powered by UltimatelySocial

## 0 Comments

Why don’t you just use the Bar Offset property in the expression properties?

That way you don’t have to use 2 dimensions.

Hi Albert,

Țhank you for your comment. You must be thinking about QlikView. Unfortunately, the same attributes are not supported in Qlik Sense

thank you

ждем книгу 🙂

This is very good, however i am struggling with different scenario. Lets assume we have following set of records:

PL:

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?

thanks

Lech

Hi Lech,

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.

cheers,

Oleg Troyansky

Hi Oleg

Can this be done with a date dimension?

To show for example the duration of projects on a timeline

Hi Chantelle,

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.

cheers,

Oleg Troyansky