Skip to Content
May 25, 2013 at 01:24 PM

Need advice on visualization


Hi all, Im running into some difficulties finding a good visualization to use. The report is a Webi 4.0 report based off of PVM (margin analysis) data. Essentially what I want is a waterfall chart, however the need is for the exact opposite functionality of the Webi waterfall. What I mean is that I need the waterfall to be based off of a single dimension and multiple key figures. So each variance (price, volume, cost, mix) are all separate key figures and I want to look at the whole picture of those variance based off of any single dimension (could be by business, or by product, or by customer....).

Originally we were able to trick the system by uploading an Excel file via Rich Client with the key figure names in a column and making that a dimension, and then creating a fairly lengthy IF statement to assign the key figure values to uploaded dimensions

=IF ( [Upload Dimension] = "Price Variance" ; [Price Variance Key Figure] ; IF ([Upload Dimension] = "Cost Variance" ; [Cost Variance Key Figure] ; .......

This works perfectly for a static view..... so here is the kicker.... We have the Price Variance key figured modified in a variable that excludes drastic outliers (one offs that would drive bad actions). This is accomplished through a series of formulas where the user can control a slider input control to determine the % price change that determines those outliers. For example if the slider is set to 35%, any item that has a price variance % greater than 35%, the Price Variance is changed to 0. For this variable to work we need to use the ForEach functionality thats tied to multiple dimensions from the source data. Since there is no way to link the uploaded dimensions to the source data, this formula has no effect, there isnt a way to link it to the context of the any of the source data.

So thats the pickle we are in, and I am open to changing the visualization (we have the exact same issue for the Heat Map visualization we created as that functionality is the same as the waterfall). However I really dont see a good visualization to use for this scenario of multiple key figures that bridge one periods gross margin to anothers (YTD vs YTD, or Current Month vs Prior Month), associated to only one single dimensional value?

Thanks for the time and any insight.

Paul Burns