topic Waterfall diagram in QlikView

Waterfall diagram

Wed, 19 May 2010 10:58:43 GMT

Hi Guys,

I want to make a waterfall graf. The first bar should be the target costs. The second bar is the cost category1 target-actual, the third is the cost category2 target-act, etc.

I know from the forum how to use offset function. The problem is the offset of the second bar should be the total planned cost. I can't calculate this in the chart, because the dimension is the cost category, and the sum of planned costs is "fileted" because of this.

Can somebody help me, please?

Waterfall diagram

Wed, 19 May 2010 11:31:11 GMT

Can you use Sum(Total PlannedCost)? That should give you the total, not matter what the filtering/dimension is.

Regards,

Waterfall diagram

Wed, 19 May 2010 20:00:42 GMT

Thank you Miguel, I was thinking about Total, but I couldn't find in the hepl, so i thought there is no such a function.

My next challange is to calculate the offset for the third bar. It is like Planned cost + category1 tg-act. I am thinking about if there is a function which returns the last bar's value.

Thank you very much.

Waterfall diagram

pover — Thu, 20 May 2010 01:05:58 GMT

How many cost categories are there? If there are few categories and they don't change very often you might want to think about not defining any dimension and only expressions that you filter with a sum(if) or better yet set analysis.

Regards.

Waterfall diagram

Thu, 20 May 2010 08:35:42 GMT

I used your suggestion sum(if) stuff. It is working fine.

Waterfall diagram

Thu, 20 May 2010 08:36:38 GMT

I forgot to say thank you.

Waterfall diagram

Thu, 20 May 2010 08:59:28 GMT

Just to add a little to this, I make quite a lot of use of waterfall charts and what I use for the offsets is generally the column references. QlikView allows you to directly reference the value of a column, so what you might have is these columns:

Sum(Sales) - with a label called Sales

Sum(Cost) - with a label called Cost

Sum(XYZ) - with a label called XYZ

Now, the first column starts at zero and therefore has no offset, the second column starts at the point where the first column ends, so in this case I could use the same expression Sum(Sales) OR I could use Column(1) OR I could use the label [Sales].

The third column should start at Sales - Cost, and again I could do this with the expressions, but I could also do it by using Column(1)-Column(2) OR I could use [Sales]-[Cost]

I find this very useful because it means you can change your expressions in the columns and not worry about the offsets, you just have to be aware of things if you change the order of the expressions or the label names.

Hope it's helpful,

Waterfall diagram

Fri, 14 Oct 2011 16:39:49 GMT

NigelWest - can you please provide an example of this?