All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.
This type of question is common in all types of business intelligence. I say “type of question” since it appears in many different forms: Sometimes it concerns products, but it can just as well concern customers, suppliers or sales people. It can really be any dimension. Further, here the question was about turnover, but it can just as well be number of support cases, or number of defect deliveries, etc. It can in principle be any additive measure.
It is called Pareto analysis. Sometimes also known as 80/20 analysis or ABC analysis.
The logic is that you first sort the products according to size, then accumulate the numbers, and finally calculate the accumulated measure as a percentage of the total. The products contributing to the first 80% are your best products; your “A” products. The next 10% are your “B” products, and the last 10% are your “C” products.
And here’s how you do it in QlikView:
You should now have a table similar to the following. In it you can clearly see the classification of different products.
In this table, there are five different expressions that you can use for Pareto analysis. The graph in the beginning of this post uses Sales and Inclusive Percentage for the bars and the line, respectively; and Pareto Class for the coloring of the bars.
Further, you may want to combine the Pareto Class and the Exclusive Percentage into one expression:
Pareto Class =
If(RangeSum(Above(Sum(Sales),1,RowNo())) / Sum(total
Sales) <= 0.8, 'A',
If(RangeSum(Above(Sum(Sales),1,RowNo())) / Sum(total Sales) <= 0.9, 'B', 'C'))
Good luck in creating your Pareto chart.
Further reading related to this topic:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.