# Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee

## Recipe for a Pareto Analysis

“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.

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

Valued Contributor II

Very useful and easy to understand article.

Thanks Henric.....

6,654 Views
MVP

Useful Post

6,654 Views
Not applicable

Very great as always - And i like that you have given an example in steps. Even understandable for the biggest rookie   Keep em coming!

Thumps up!

6,654 Views
Partner

Really useful. I was looking for a method to calculate the ABC sections and although I haven't found a way to calculate that in the fly (i.e. without and accumulated chart)... at least this method looks nice.

6,654 Views
Employee

Also I have tried to find a way to calculate the Pareto class on the fly (so you can use this as a calculated dimension), but have not succeeded. So I don't think it is possible, given the existing set of functions. But having it as expression is possible, as I have shown in the post above.

HIC

Edit: The above answer was correct when it was made, but things have changed. Now it is possible to calculate the Pareto class as dimension. See Recipe for a Pareto Analysis – Revisited

6,654 Views
Not applicable

Hi Henric,

Pareto Analysis is Excellent task .I am not getting Accumulated Sales value . i am using the Same Expression Which u have used .Can u help me

6,654 Views
Not applicable

If  I use the Expression then i m getting the above values .May i know the reason .

6,654 Views
Employee

Without the data, I can only guess. And my guess is that you have not used a sort expression to sort the rows of the chart. (The second bullet in the description above.) It does not work if you just sort by y.

HIC

6,654 Views
Esteemed Contributor

Very useful and easy to understand article.

Thanks Henric....

6,654 Views
Not applicable

Hi Henric ,

Your guess is Absolutely Correct .I have used same data which u have used in this article .i am getting different Inclusive and Exclusive Percentages . Can u please help me and can u tell me step by step Process for Creating Chart which u have created in  article.

Regards,

Divya

6,654 Views
Employee

All you need to do, is to make a copy of your table, change this into a Combo chart with your "Sales" expression as bars and your "Inclusive Percentage" as line. Disable the "Accumulated Sales" and make the "Exclusive Percentage" and the "Pareto Class" invisible. The conditional background color should be on the "Sales" expression.

HIC

6,654 Views
Not applicable

Hi Henric ,

I got it.Thanks  a lot for giving with a Good Example .

6,654 Views
Not applicable

Thanks a lot Henric, it is very useful, looking for similar discussion in Future…

6,654 Views
Contributor

I made this example into our production sales of films rented by ours customers and given it to the Controllers

I have approx 65000 rec i each month and it seems to me that about data concatenated for one year, will have quit have a execution time on approx. 15 sec.

CanalDigital

6,654 Views
Not applicable

A great example and easy to understand.

6,654 Views
New Contributor III

Hi Henric,

thank you very much for sharing this.

I was struggling with the Inclusive Percentage Line as it was scaling together with the Dimension and the backgroung color but at the end I got it right.

Very interesting post!

Best Regards,

Alen

6,654 Views
Contributor

Iam not getting the Background color to work. I tried to put it as an separate expression but its not giving the colors.

6,654 Views
Not applicable

Hi,

In Expressions tab  u will get + symbol before your Expression . just click on that u will find  7 chart attributes Select Back ground color and write the Expression .

Regards,

P.Divya

6,654 Views
Contributor

Thanks, very Useful

/TH

6,654 Views
New Contributor III

Thanks, good job.

Regards

Davide

6,654 Views
New Contributor III

Hi Henric,

Happy New Year.

I have one easy question. How you get the 80% threshold line you have in the graph as dotted line?
I have a green continuous line but I seem not finding any dotted style set up.

Could you point me out to the right direction?

Many Thanks,

Alen

6,654 Views
Employee

Chart Properties -> Presentation -> Reference Lines

HIC

6,654 Views
New Contributor III

Thank you Henric,

the reference was not working on the secondary Y and I had worked around with an expression which cannot be as dotted line. With your input I now I fixed the reference on secondary Y and it works.

Thank you!

Alen

6,654 Views
Not applicable

Hi Henric,

Thank you very much for your article.

Very-very-very useful for me!

And small question: how to make invisible into pivot table intermediate expressions ("Accumulated Sales", "Inclusive Percentage", ...)? Ie I want to remain visible only two fields: "Product Name" and "Pareto class".

Oleg

6,654 Views
Employee

The Pivot Table doesn't have the concept of enabled, invisible expressions. An expression is either enabled and visible, or disabled and invisible. So you need to disable the expressions you don't want.

The Pareto Class depends on the Exclusive Percentage only. All other expressions can be disabled or deleted. If you want to disable the Exclusive Percentage also, you can do that too, but then you need to re-write the Pareto Class like I have done in the end of the article.

Good Luck!

HIC

6,654 Views
Not applicable

Thank you, Henric!

Now I understand about the visibility expressions.

And the last question: Do you plan to publish in the near future about the XYZ-analysis?

Regards,

Oleg

6,654 Views
Employee

I hadn't planned to do so, but I can look into it. It could be a good topic for a post.

HIC

6,654 Views
Not applicable

I'm sure - it will be wonderful and useful for many to see the implementation of the XYZ-analysis performed by such a specialist like you. Especially interesting combination of these types of analyzes (ABC and XYZ). Personally, I'll wait impatiently this publication.

Regards,

Oleg

6,654 Views
Partner

Great example Henric !

I want to combine this with FMS Analysis based on Sale Qty such that each product simultaneously gets assigned two classes -- A/B/C and F/M/S and then count the no of items in each combination, eg how many products belong to AF, AM, AS, BF, BM etc. based on sale period selected. How could i do that?

Thanks and Regards

Rameen

6,654 Views
Esteemed Contributor