Waterfall Chart With Dimensions?

[#1]

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.

[#2]

This is what I see:

[#3]

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.

[#4]

Sure, here it is.

[#5]

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)))

[#6]

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.

[#7]

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.

[#8]

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

[#9]

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

PC

[thierrytt1102]

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