Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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

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.

 

Pareto chart.png

 

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.

 

Table.png

 

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

128 Comments
ali_hijazi
Partner - Master II
Partner - Master II

and can I put 0.8 instead of 0.5 for (Rank(Sum(Sales),1)-1) / Count(distinct total Product)

so that the result is the same as that of the first article?

0 Likes
670 Views
hic
Former Employee
Former Employee

You can change it to whatever you want, but the ranking method will not return the same result as the pareto method.

Further, 0.5 in the ranking method (the better half of the customers) often corresponds to roughly 0.8 in pareto.

0 Likes
670 Views
ali_hijazi
Partner - Master II
Partner - Master II

and this is always over total number of products?

can it be by brands ?

0 Likes
670 Views
hic
Former Employee
Former Employee

If you use the total qualifier, you can get it by brand or product group, e.g.

(Rank(total <ProductGroup> Sum(Sales),1)-1) / Count(distinct total total <ProductGroup> Product)

0 Likes
670 Views
ali_hijazi
Partner - Master II
Partner - Master II

I have a question outside this topic , may I ask you and you help me in return

0 Likes
668 Views
hic
Former Employee
Former Employee

Post it as a separate question in the discussion forum and tag me. If I have time, I will respond.

/HIC

0 Likes
668 Views
ali_hijazi
Partner - Master II
Partner - Master II

Hello got a question on this

business-wise is the following fine?

I select a brand and got the following for each product under the selected brand:

pareto.png

so the first four products represent the 80% of net sales for the selected brand?

or shall I compare it to total net sales?

please advise

0 Likes
668 Views
yurgelmartina
Contributor
Contributor

Hi Henric,

If I have this expression:

count({$<FLAG_RUPTURA = {'RUPTURA'} >} FLAG_RUPTURA)/sum(TOTAL)

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


How Can I use in the expression of accumulated Sales??


RangeSum(Above(Sum(count({$<FLAG_RUPTURA = {'RUPTURA'} >} FLAG_RUPTURA)/sum(TOTAL)), 0, RowNo())) --> Like thtat doesnt work

0 Likes
668 Views
Anonymous
Not applicable

Hello Sir,

thank you very much for this recipe. I am looking for this for a moment.

I am training on QlikView and i am beginner.

I started follow your steps but i had an issue. Can you help me please?

I cannot choose "sort by ewpression". Do you know why?

Thank you for answering.

QV.PNG

0 Likes
668 Views
Anonymous
Not applicable

Hello Sir,

i followed chat you said but i do not know why Exculsive percentage were all nulls although i insered the same formula.

Thank you for help.

Best regards,

Alâa Eddine.Act.PNG

0 Likes
668 Views