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.

[giakoum]

hope this helps.

[MK_QSL]

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

[#4 · Accepted solution]

Hi Paul,

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

Thanks

AJ

[#5]

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.

[#6]

And I also tried:

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

[#7]

Please check out the attached.

Is this what you want.

[#8]

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.

[#9]

Ahh, I got it:

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

[#10]

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.