The purpose of the Pareto chart is to highlight the most important among a (typically large) set of factors. In quality control, it often represents the most common sources of defects, the highest occurring type of defect, or the most frequent reasons for customer complaints, and so on. Wilkinson (2006) devised an algorithm for producing statistically-based acceptance limits (similar to confidence intervals) for each bar in the Pareto chart.I was asked to create such a chart in WebI and did not find a direct clue on how to do this via google. So here goes my first work related article on my blog.
I have a data warehouse based on SAP IDES data and I’m looking at revenue. For this example we would like to see the products with the highest revenue.
If you would do this in excel, then you start with a running sum, and thereafter divide this running sum with the total sum of the revenue. I tried this way in WebI but with no luck; I did not manage to get the total sum on the rows.
After some fiddling I found the percentage function:
This gives us:
The only thing left is to cumulate the percentages with the runningsum function and we’re almost done:
Create a variable based on the above function and then change the table into a chart with vertical bars and a line:
Uncheck the ‘3D look’ in the display properties. Then click ‘View structure’ in the reporting toolbar and sort descending on Revenue, click ‘View results’ to return to the chart.
By default the percentage will max to 120%, we can limit this to 100% in the scale properties of the Z-axis:
The result is a nice pareto chart: