Waterfall chart using dimensions

[mhassinger]

I need to make a waterfall chart where the first bar is the forecast, the last bar is the actuals, and the bars in between represent various geographies or products. I looked at the waterfall example in the data visualization application, and I see how that works - you don't use any dimensions, and you create an expression for each bar.

The problem with this is that I'd like to be able to use a drill down group like I have in other objects. For example, if the waterfall bars are forecast, usa, europe, asia, africa, actuals, the user would like to be able to click on the europe bar and then see forecast, UK, Germany, France, ... , actuals.

So is there another way to make a waterfall chart where the bars in between the extreme left and right are created dynamically through dimensions, allowing for drill down and/or cycle groups? I really want to avoid having to manually create expressions for every product and every country up and down the hierarchies.

[mhassinger]

So I take it there is no way to accomplish this?

[jonathandienst]

Hi

See my blog for a demo and an explanation of how to implement a waterfall chart using a normal bar chart that should meet your needs.

Waterfall demo

Hope that helps

Jonathan

[mhassinger]

Jonathan,

Thanks for the link. I am following the instructions in the blog and looking at the samle QVW, but I'm not able to replicate the results. I've attached a simple recreation of my data model with two waterfalls. The first is the "normal" way I saw in the Data Visualizations.qvw file. The second is using the expressions, and offsets from your article (as near as I can tell, anyway).

I am trying to get the second object to look like the first. The issues I'm seeing are that the if block in the first expression doesn't seem to be working (so no big bar at the end), the offsets are causing some of the bars not to "connect" by starting where the last one stopped, and because there is no actual data associated with the "Forecast" and "Actuals" column, I have to set "show all values" in the Dimension tab, which also causes that last "-" column to appear.

If you could take a look at my file and see what I'm missing, I'd really appreciate it!

[jonathandienst]

Hi

Needed to modify the approach slightly, but here is the file with the waterfall chart. Took out the second table to prevent a minor issue if the year was not selected, but I assume that table was just needed for the "old" waterfall chart.

Hope that helps

Jonathan

[mhassinger]

Jonathan,

Thanks for the help - that's a lot better! I still have a couple of nagging issues, and maybe I'm just asking for too much, but ideally I do need to keep that Waterfall table separate. The actual fact table I'm working with is a lot more complex than my example and the idea of introducing extra records into it isn't ideal. And since Area is a user selection, I don't want Forecast and Actuals showing up as choices.

I've reattached a version with the split again, and I'm wondering if there is a way around these two issues:

1. I need to remove the blank space above all the areas in the selection box.

1. When I select on area, date, or other potential dimensions, I'd like to keep the forecast and actuals bars in the chart. For example, if I select the United States, I'd want to see 3 bars - Forecast related to US, the US difference, and the Actuals related to US. If I select 2010, I'd want to see Forecast, all areas, and Actuals for the 2010 data.

Any ideas?

[Anonymous]

Nice trick Jonathan.

I will try to replace the approach I have been using with multiple expressions that each have a bar offset set from the cummulative total of the previous expressions. This is because this approach doesn't allow users to drill down into these expressions.

Can you advise whether this should work with two dimensions, such as year and area in your example, or in my case month and P&L report row (income, direct costs, indirect costs etc.)

Specifically I am wondering whether the cumulative total from the last area of one year can follow through to the first area of the next year.

Jonathan