topic Re: Waterfall Chart With Dimensions? in QlikView

Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 20:31:30 GMT

Hi all,

I'm trying to create a waterfall chart with, for example, spend by office. I'd like to have the dimension (office) be dynamic because I'm not sure how many cities the data will include when it is loaded into the document. The only waterfall examples I've seen are where the dimension is excluded and each expression acts as somewhat of an individual dimension. In that case, it's easy to choose an offset because you have more control of the surrounding expressions.

Is it possible to use a dimension when creating waterfall charts, and use a dynamic offset in relation to the other bars? Below is an example of the graphic I'd like to achieve.

Re: Waterfall Chart With Dimensions?

giakoum — Wed, 29 Jan 2014 20:39:22 GMT

hope this helps.

Re: Waterfall Chart With Dimensions?

MK_QSL — Wed, 29 Jan 2014 20:43:39 GMT

Create a Bar Chart

Dimension : CITY

Expression : SUM(Value)

Click + sign of Expression and Select Bar Offset...

Type Below in Definition of Bar Offset

=Sum(Total Value) - Sum(Value)- RangeSum(Above(Sum(Value),1, RowNo()-1))

Hope this help...

Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 20:45:37 GMT

Hi Paul,

Please check out the attached file. It might point you in the right direction.

Thanks

Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:26:38 GMT

You guys are geniuses!

One more question - what if I want the offset on the "Total" bar to be zero? I tried something like:

If(rowno()=1 or RowNo() = max(RowNo()),0, [other expression])

but it didn't work.

Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:29:05 GMT

And I also tried:

If(rowno()=1 or RowNo() = count(DISTINCT OfficeVariable),0, [other expression])

Re: Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:40:54 GMT

Please check out the attached.

Is this what you want.

Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:44:51 GMT

This doesn't quite do it because the Total bar still has a non-zero offset. I want the offset on the Total bar to be zero, the same as the first row item.

Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:49:53 GMT

Ahh, I got it:

If(rowno()=1 or rowno()-1 = count(DISTINCT Office),0, [expression])

Re: Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:50:45 GMT

Lol. It actually appears as if total has an offset but it doesn't. Its offset is zero same as the first one.

I have added axes to it. This will look better now.

Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:53:32 GMT

This is what I see:

Re: Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 21:58:09 GMT

This is my view. Did you change the offset expressions. How can it be different for you. I am puzzled. Do you mind reattaching it. I can open it and check.

Re: Re: Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 22:04:32 GMT

Sure, here it is.

Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 22:15:04 GMT

When i opened it I am seeing a chart similar to the image that i attached. How can it be different for you?

Do you mind using this expression in the baroffset:

If(rowno()=1 or rowno()=0 ,0,rangesum (above(sum(Value),1,rowno()-1)))

Re: Re: Re: Waterfall Chart With Dimensions?

Wed, 29 Jan 2014 22:27:35 GMT

Hmm that's interesting. The image you posted is exactly what I want, but I'm still seeing the total offset for some reason. In addition, this is the offset formula when I download the file (it didn't change from the original):

If(rowno()=1,0,rangesum (above(sum(Value),1,rowno()-1)))

I tried your new condition, If(rowno()=1 or rowno()=0 , and it still doesn't achieve the result I want for some reason. Besides the Total bar, all others are correct.

Re: Re: Re: Re: Waterfall Chart With Dimensions?

Thu, 30 Jan 2014 03:05:23 GMT

Fixed it buddy. Finally found out what was going on. I was using QV 10 and it was working fine but when i went home and tried on my personal laptop which has QV 11 I was seeing the issue you were talking about. Some of the features are slightly different so I had to make some changes to accommodate those and it should work now.

I didn't realize there was this much of an impact between 10 and 11.

Hope its all good now. If you see any issues please holler back.

Re: Re: Re: Re: Waterfall Chart With Dimensions?

Thu, 30 Jan 2014 03:25:26 GMT

This is excellent. Looks perfect. Thanks for all the help!

Re: Waterfall Chart With Dimensions?

Fri, 29 Aug 2014 00:02:10 GMT

Hi all:

Thank you so much for your time. This is a very good post!!

I have a further question.

How is the RowNo() for each value in the dimension decided?

I want the waterfall to be sorted in term of Y-value.

If I only select Y-value, the RowNo() is not the same order as the Y-value.

I can only make them in the same order using the expression.

How can I make this dynamic?

Thank you very much

Re: Waterfall Chart With Dimensions?

thierrytt1102 — Mon, 07 Sep 2015 10:18:16 GMT

Hi ,

I would have like to go a little bit further. What if we have a drill-down group?

Do we have to adapt your variable using smthing like : if(getpossiblevalues(Level1)=1, then count(distinct level2), etc...?

Thx for your reply