So, how do we go about this.

CREATE BAR CHART

Create a bar chart with the dimension you require for the X axis, and the following expressions:

- Total (this is the value for the final, total bar - green in the example above)
- Value (this is the value for the blue and red bars - rename as appropriate)
- Cum (this is a hidden value which tracks the cumulative value)

Use the expressions:

- Total: If(Dim1 = 'Total', Sum(Total Value1))
- Value: Sum(Value1)
- Cum: RangeSum(Above(Column(3)), Column(2))

Set Cum to Invisible on the Expressions tab of the chart properties.

MAKE THE WATERFALL

The trick now is to use the bar offset on the Value expression and use this expression:

=If(IsNull(Above(Column(3))), 0, Above(Column(3)))+if(Column(2)<0,Column(2),0)

Click the + sign next to the expression name in the Expressions tab to see the Bar Offset.

That should do it. You can download a demo here : Waterfall demo

To colour the negative values red, set the Background Color for Value to the expression:

=If(Column(2)<0, RGB(200,110,130))

Have fun!

Hi Jonathan,

ReplyDeleteI was looking for waterfall charts and found your blog. I just started working with QV and it is a total mess in my head. I have tried to modify your demo chart so that I could use it for my reports, but I have two issues which I cannot solve by myself. 1. I have replaced in the script Line1, Line2 etc. with Pr.year, Domestic and Export (deleted the other Lines) and Total is my Actual year. How come that in the chart my first bar became Actual (i.e. Total) and the last became Pr Year (i.e. Line 1). 2. The two bars in the middle are starting from 0 and not from Pr Year's value as it should be. I think my issues have to do with the bar offset formula and the Cum expressions but I am too inexperienced to understand them and find my mistake. Could you pls help me or give me some advice where to read about it?

Thanks in advance!

Best regards, Irina

Thanks Johathan. This is very helpful. Can you also advise how to show the actual values on the bars?

ReplyDeleteThat was a real help. For my chart I had change the bar offset expression to exclude the 'if' clause added to the principle clause, but besides that it worked just as I needed.

ReplyDelete