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

[hic]

For the [Inclusive Percentage] I use

     RangeSum(Above(Sum(Sales), 0, RowNo())) / Sum(total Sales)

and for the Colors I use

     If([Inclusive Percentage] <= 0.8, LightGreen(), If([Inclusive Percentage] <= 0.9, Yellow(), LightRed()))

HIC

[#4]

Thats perfect ! many thanks !!!!

[#5]

Hi Henric,

Is the process of creating a Pareto chart in Qlik Sense the same as the one for QlikView? If not, can u please help me with a step by step Process for Creating a Pareto Chart in Qlik Sense?

[hic]

You can use exactly the same instruction. It will work.

HIC

[Anonymous]

A master Chef Recipe , very good article

[#8]

Henric Cronström schreef:

With the above method, it is unfortunately not possible to show subtotals or to use the ABC-classification as a dimension.

However, if you use a different definition of the ABC-classification, it's possible. E.g. use the following as calculated dimension:

     =If(Aggr(Rank(total Sum(Sales))< 0.4*Count(distinct total ProductName),ProductName),'A',

       If(Aggr(Rank(total Sum(Sales))< 0.6*Count(distinct total ProductName),ProductName),'B',

       'C'))

I.e. if the product ranks within the best 40% of the products (responsible for most of the turnover), then the product is an 'A' product. And if it is between 40% and 60%, it is a 'B', etc.

With this definition you can use subtotals.

HIC

I know you state that it's a different definition but this is a false formula

Counting the ProductNames is not what ABC is about. You can't says that if there are 100 products, 40 of them should have class A as it says nothing about their gross margin / turnover.

[hic]

What you point out is semantics.

As I see it, a Pareto analysis must consider the turnover: It should show the products that are responsible for the first 80% of the turnover. But an ABC analysis - which is a wider concept - can have a different, more general definition. For example, the best ranked products.

In any case, it is up to you to choose which definition to use. If you choose the rank, and not the turnover, the above formula is correct.

HIC

[#10]

Ok you are right. Rank does not account for skewed data which actually is the whole idea of doing a Pareto 80-20.

As I understand your formula only works for simple ranks.

[#11]

Thanks, useful post.

Is there any way For individually counting for A,B and C?

[robert_mika]

Informative.Thank you