Recipe for a Pareto Analysis

[hic]

“Which products contribute to the first 80% of our turnover?”

[hic]

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:

1. Create a pivot table and choose your dimension and your basic measure. In my example, I use Product and Sum(Sales).
   
   
2. Sort the chart descending by using the measure Sum(Sales) as sort expression. It is not enough just to check “Sort by Y-value”.
   
   
3. Add a second expression to calculate the accumulated sales value:
        RangeSum(Above(Sum(Sales), 0, RowNo()))
   Call this expression Accumulated Sales. The Above() function will return an array of values – all above values in the chart – and the RangeSum() function will sum these numbers.
   
   
4. Create a third expression from the previous one; one that calculates the accumulated sales in percent:
        RangeSum(Above(Sum(Sales), 0, RowNo())) / Sum(total Sales)
   Format it as a percentage and call it Inclusive Percentage.
   
   
5. Create a fourth expression from the previous one; one that calculates the accumulated sales in percent, but this time excluding the current row:
         RangeSum(Above(Sum(Sales), 1, RowNo())) / Sum(total Sales)
   Format it as a percentage and call it Exclusive Percentage.
   
   
6. Create a fifth expression for the ABC classification:
         If([Exclusive Percentage] <= 0.8, 'A', If([Exclusive Percentage] <= 0.9, 'B', 'C'))
   Call this expression Pareto Class. The reason why the Exclusive Percentage is used, is that the classification should be determined by the lower bound of a product’s segment, not the upper.
   
   
7. Create a conditional background color, e.g.
         If([Pareto Class] = 'C', LightRed(), If([Pareto Class] = 'B', Yellow()))

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.

HIC

Further reading related to this topic:

Recipe for an ABC Analysis

Recipe for a Pareto Analysis – Revisited

[#3]

hello, i don't find the place to set the color to paretoclass

[hic]

Chart properties - Expressions - The small plus-sign to the left next to the expression - Background color

HIC

[jafari_ervin]

why the result of this formula and 80-20 in dimension limit are different?

[anderseriksson]

If it's the difference between inclusive and exclusive percentage you are asking about it is because you want to include the bar that goes beyond 80% when doing the coloring.

The colored bars are those that precisely passes 80%.

You can't just compare with 80%, that would exclude the last bar that passes 80%.

[alexandros17]

Useful and enlightening as usual.

Thanks

[warfollowmy_ver]

Thank you for this information! I did it, it works. But when you have the day of sale 30 000 positions and statistics for the 5 years prior to the date ... In general, even for a week the server just stops, since the calculation is carried out in a loop in lines. If, for example take a simple table as an option accumulation, all he said instantly. But it also limits. What will advise how to implement the ABC analysis with giant data?

[#9]

Hi Henric,

Great posts you are providing. I would like something a bit more than the basic Pareto. I would like to compare orderline pareto (or frequency pareto) with volume pareto (or quantity pareto).

Either in a scatter chart or pivot this is what I try to accomplish;

	A Volume	B Volume	Volume C
A Line Count	12	12	40
B Line Count	23	50	100
C Line Count	15	36	200

Where the numbers a SKU (or product, item anything counts) and the categories are defined as you explained;

1. (= column headers)

=If(RangeSum(Above(sum([Line Volume (l)]),1,RowNo())) / sum(total [Line Volume (l)]) <= 0.8, 'A',

         If(RangeSum(Above(sum([Line Volume (l)]),1,RowNo())) / sum(total [Line Volume (l)]) <= 0.9, 'B', 'C'))

2. (= row headers)

=If(RangeSum(Above(Count(Order_Number),1,RowNo())) / Count(total Order_Number) <= 0.8, 'A',

         If(RangeSum(Above(count(Order_Number),1,RowNo())) / count(total Order_Number) <= 0.9, 'B', 'C'))

So in the most easy way this formula's above would be my dimensions and distinct SKU count would be my expressions, but unfortunately it doesn't work like that. Simply because the sortation rule is key in the result for this graph.

I hope you understand the questions and have a fitting solution.

Thanks

[marciomgm]

Thank you Henric hic!!

Very interesting post!

It worked perfect!

Now I need to calculate the ABC curve by the amount of products.

Example I would like:

Table:

A   10%   200

B    30%  1500

C    60%   8000

I want to calculate the amount of product (SKU code) for each curve, based on sales

count (produto) ...   how about it? how do the full expression?

[ali_hijazi]

is there a way to have the pareto classes (A,B,C) as dimensions ?

so that user selects A and see the related products?

I tried to use this approach with the class function but it doesn't seem to work well

please advise

[hic]

No, it is (currently) not possible to use a pareto-based definition for the dimension. At least, I haven't found one...

You can however use a rank-based definition for the dimension. See Recipe for an ABC Analysis

HIC