# Qlik Design Blog

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

Announcements
Employee

## Recipe for a Pareto Analysis – Revisited

“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 any dimension, e.g. customer, supplier, sales person, etc. Further, here the question was about turnover, but it can just as well be e.g. number of support cases, or number of defect deliveries, etc.

It is called Pareto analysis or ABC analysis and I have already written a blog post on this topic. However, in the previous post I only explained how to create a measure which showed the Pareto class. I never showed how to create a dimension based on a Pareto classification – simply because it wasn’t possible.

But now it is.

But first things first. The logic for a Pareto analysis is that you first sort the products according to their sales numbers, 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, your “A” products. The next 10% are your “B” products, and the last 10% are your “C” products. In the above graph, these classes are shown as colors on the bars.

The previous post shows how this can be done in a chart measure using the Above() function. However, if you use the same logic, but instead inside a sorted Aggr() function, you can achieve the same thing without relying on the chart sort order. The sorted Aggr() function is a fairly recent innovation, and you can read more about it here.

The sorting is needed to calculate the proper accumulated percentages, which will give you the Pareto classes. So if you want to classify your products, the new expression to use is

=Aggr(
If(Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),1,RowNo()))<0.8, 'A',
If(Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),1,RowNo()))<0.9, 'B',
'C')),
(Product,(=Sum({1} Sales),Desc))
)

The first parameter of the Aggr() – the nested If()-functions – is in principle the same as the measure in the previous post. Look there for an explanation.

The second parameter of the Aggr(), the inner dimension, contains the magic of the sorted Aggr():

(Product,(=Sum({1} Sales),Desc))

This structured parameter specifies that the field Product should be used as dimension, and its values should be sorted descending according to Sum({1} Sales). Note the equals sign. This is necessary if you want to sort by expression.

So the Products inside the Aggr() will be sorted descending, and for each Product the accumulated relative sales in percent will be calculated, which in turn is used to determine the Pareto classes.

The set analysis {1} is necessary if you want the classification to be independent of the made selection. Without it, the classification will change every time the selection changes. But perhaps a better alternative is to use {\$<Product=>}. Then a selection in Product (or in the Pareto class itself) will not affect the classification, but all other selections will.

The expression can be used either as dimension in a chart, or in a list box. Below I have used the Pareto class as first dimension in a pivot table.

If you use this expression in a list box, you can directly select the Pareto class you want to look at.

The other measures in the pivot table are the exclusive and inclusive accumulated relative sales, respectively. I.e. the lower and upper bounds of the product sales share:

Exclusive accumulated relative sales (lower bound):

=Min(Aggr(
Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),1,RowNo())),
(Product,(=Sum({1} Sales),Desc))
))

Inclusive accumulated relative sales (upper bound):

=Max(Aggr(
Rangesum(Above(Sum({1} Sales)/Sum({1} total Sales),0,RowNo())),
(Product,(=Sum({1} Sales),Desc))
))

Good luck in creating your Pareto dimension!

HIC

Further reading related to this topic:

The sortable Aggr function is finally here!

Recipe for a Pareto Analysis

Recipe for an ABC Analysis

Not applicable

That's awesome news, HIC!

I was just working in a case similar to this. I tried the function and it worked perfectly! The only change I made was removing the 1  from the set analysis because I want my ABC to be dynamic according to my selections. Do you think this could be a problem?

Once again, thank you very much for sharing this!

10,687 Views
Employee

If you really want it to be dynamic, it should cause no problems. However, it will mean that the classification will change the moment you select one of the Pareto groups. So a second click on a Pareto class will reduce the data sample further. This will be confusing: "Now, how many times did I click on class A?"

An alternative that I think is better is if you use {\$<Product=>} as set expression (assuming that the Pareto classification is made on the field Product). Then you will have a dynamic classification that is based on all fields except the Product.

HIC

10,687 Views
Not applicable

That's is a concern and I believe it won't be possible to include the Pareto classifications into the set expression haha.

The reason I mentioned the need to make the analysis dynamic is, for example, when I have sales representatives and I want to analyze each one individually, i.e., each sales representative will have a different set of clients in the Pareto analysis and therefore different classifications.

Stefano Draghi

10,573 Views
MVP

This is amazing and would come in very very handy. I have people waiting for a solution for this exact problem who had to look into dirty workarounds. I have already guided them to take a look at this new functionality.

Thanks for sharing,

Sunny

10,573 Views
MVP

Here is an example which was not something not doable before, but now it is

Re: ABC Analysis in Qlikview

10,573 Views
Partner

Notice in Qlik Sense that the Pareto Classes do not appear in the drop-down.

Is this a bug?

10,573 Views
Employee

Looks like it... Thanks for pointing it out. We will investigate.

10,573 Views
Partner

I'm just playing around with this feature, and it seems it solves another old Qlik limitation for me.

Maybe I'm still in Chistmas mood, and have overlooked something, but the expression

=(aggr(dual(only(Artikelname),RowNo(total)) ,Firma,([Artikel-Nr],sum(Sales),DESC)))

allows me to sort a Pivottable to always show the Top Product per Company.

Before that, the sort was always globally, and therefore the product "Cote de Blaye" would always be the first line for each Company, as it is overall the most sold product.

The way to get there is a little bit cumbersome (I first have to create a straight table, change the sort order of dimensions, then change it back to a pivot table). But beside that it works on my dataset in QlikView 12.10 SR1.

It would be nice if someone can test this with another dataset.

I will post a video how I create the pivot table in a minute!

10,573 Views
Partner

Here how I created the pivottable

10,325 Views
Partner

And here the .qvw file:

content.heldendaten.eu/Nordwind_SortableAggr.zip

10,325 Views