Qlik Community

Qlik Design Blog

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

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.

Recipe for an ABC Analysis

 

An ABC analysis is a dynamic bucket classification of e.g. products, based on some property, usually the sales number. The best products are your "A" products and the worst are your "C" products.

It is used in all types of business intelligence applications and can appear in many different forms: It can concern any dimension, e.g. customer, supplier, sales person, etc. and be based on any measure. The sales number is one example, but it can just as well be e.g. number of support cases, or number of defect deliveries, etc.

One way to make an ABC analysis is to use a Pareto analysis where the classification is based on the accumulated number after the entities have been sorted according to their numbers. The products contributing to the first 80% are usually the A products.

However, the Pareto analysis, as described in the above blog post, is sometimes limiting: It is for instance not easy to use several dimensions, and it is not possible to define the ABC classes as a dimension. Hence, it is sometimes better to use an alternative classification function:

The Rank.

QlikView has a Rank() function that is well suited for this purpose. With it, you can rank any dimension according to any expression. You can use several dimensions and you can define your ABC classes as dimensions. The logic is that you calculate a relative rank, i.e. you divide the rank of the product with the total number of products:

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

If this number is lower than 0.5 the product belongs to the better 50% and thus to the “A” products. Similarly you can use 0.75 as a limit for belonging to group “B”. The result will be very similar to a Pareto analysis.

Sense Bar chart.png

 

One possibility is to use colors to display the classification:

  1. Create a bar chart and choose your dimension and your basic measure. In the example below, I use Product and Sum(Sales) labeled as “Sales”.

  2. Set the color of the bars to
    If((Rank(Sum(Sales),1)-1) / Count(distinct total Product) < 0.50, RGB(140,170,200),
    If((Rank(Sum(Sales),1)-1) / Count(distinct total Product) < 0.75, RGB(255,200,0), LightRed()))
    In QlikView you do this under the expression “Background color” and in Qlik Sense you do it under “Appearance” – “Colors and Legend” for the object.

 

But you can also use this method to create a field or a calculated dimension, which means that you can make the ABC classes selectable:

     Aggr( If((Rank(Sum(Sales),1)-1) / Count(distinct total Product)< 0.50, 'A',
          If((Rank(Sum(Sales),1)-1) / Count(distinct total Product)< 0.75, 'B', 'C')),
          Product)

As Dimension.png

 

Finally, if you want to use ranking in a two-dimensional chart, you can use the same logic. However, you must first decide how the rank should be calculated. Normally you would want the ranking to be done within each group defined by the second dimension, i.e. per column in a pivot table:

Two-dimensional per column.png

 

The above chart shows sales per product and customer. The colors define the classes and the rank and the count is done within each column, i.e. the products are classified within each customer. The following expression was used:

     If((Rank(Sum(Sales),1)-1) / Count(distinct total <Customer> Product)< 0.50, RGB(140,170,200),

     If((Rank(Sum(Sales),1)-1) / Count(distinct total <Customer> Product)< 0.75, RGB(255,200,0), LightRed()))

 

But you may also want to do the ranking within each group defined by the first dimension, i.e. classify the customers within each product. Then you need to swap place of Customer and Product in the formula, and you need to use HRank() instead:

     If((HRank(Sum(Sales),1)-1) / Count(distinct total <Product> Customer)< 0.50, RGB(140,170,200),

     If((HRank(Sum(Sales),1)-1) / Count(distinct total <Product> Customer)< 0.75, RGB(255,200,0), LightRed()))

 

Two-dimensional per row.png

 

Good luck in creating your ABC analysis!

HIC

 

Further reading related to this topic:

Recipe for a Pareto Analysis

34 Comments

An excellent, concise blog as always.

0 Likes
2,239 Views
Not applicable

What is the font you used in the first chart?

0 Likes
2,239 Views

The first chart is created in Qlik Sense, so the font is not exposed in the UI. It is defined in a css file somewhere... Frankly, I don't know which font it is.

HIC

0 Likes
2,239 Views
senarath
Contributor III

Hi HIC,

Tell me if I want to apply above for a set analysis such as below, how would be the expression.

Sum({$<[Business Date]={'>=$(=Date(vStartDate)) <=$(=Date(vEndDate))'},Type={Actual},Year=,Quarter=,Month=,Week=,Day=>} [Room Nights])

many thanks.

0 Likes
2,239 Views

A Set Analysis expression is in principle the same as a selection. So, if you wanted to select the "A" Products according to the above definition, you could do it by using

     Sum({$<Product={"=(Rank(Sum(Sales),1)-1) / Count(distinct total Product)<0.5"}>} Sales)

In other words: You make a search in the Set analysis expression, picking out the relevant Products.

HIC

0 Likes
2,239 Views
herbert_beck
New Contributor III

Dear Henric,

you (c)should use (1+(Rank(Sum(Sales),1)-1)) / Count(distinct total Product) and <= instead of < otherwise your last product gets relative rank 0.

Kind regards,

Herbert

2,239 Views
juan_escobar
Contributor II

hic is the first graphic from QlikSense? or can I plot the value in the bar like the first one and above the bar for the rest in QlikView?

0 Likes
2,239 Views

@ Herbert Beck: Not sure I agree... In the chart below, the 1st formula is the one from the blog post above; the 2nd one is yours; and the 3rd is what you should use if you want the relative numbers to span the entire range between 0 and 1.

Relative rank.png

However - if you only have one single value of X, the 1st formula will assign it to class A, the second will assign it to class C, and the third one will fail to calculate. I think that a single value should belong to class A.

@ Juan Escobar: The first graphic is indeed from Qlik Sense. It is not possible to place the numbers this way in QlikView.

HIC

0 Likes
2,239 Views
cleblois
New Contributor III

Hi HIC,

I'm trying to practice on your examples, but I probably missed something here

I want to build a calculated dimension in Sense that will allow me to sort out the products for which we're selling more than what we quoted.

Below my formula - which was a straight copy of yours - but I only get A

Aggr( If((Rank(Sum(([Qty Billed]-[Qty Opp])),1)-1) / Count(distinct total [Part Number])< 0.50, 'A',

          If((Rank(Sum(([Qty Billed]-[Qty Opp])),1)-1) / Count(distinct total [Part Number])< 0.75, 'B', 'C')),

          [Part Number])

What am I missing?

0 Likes
2,239 Views

Not sure...

I would try the following:

1) Change your formula to

Aggr( If((Rank(Sum(([Qty Billed])-Sum([Qty Opp])),1)-1) / Count(distinct total [Part Number])< 0.50, 'A',

          If((Rank(Sum(([Qty Billed])-Sum([Qty Opp])),1)-1) / Count(distinct total [Part Number])< 0.75, 'B', 'C')),

          [Part Number])

The reason is twofold: If one of the two quantities is NULL, the minus will not work. Secondly, if the two quantities are in different tables, it will not calculate correctly.

2) Create a table with Part Number as dimension and

- Sum(([Qty Billed])-Sum([Qty Opp])

- Rank(Sum(([Qty Billed])-Sum([Qty Opp])),1)-1

- (Rank(Sum(([Qty Billed])-Sum([Qty Opp])),1)-1) / Count(distinct total [Part Number])

as Measures (Just to debug)

HIC

0 Likes
2,239 Views
cleblois
New Contributor III

Thanks for the support, getting too tricky for more, I'll get a younger guy on it

0 Likes
2,239 Views
Not applicable

Thanks Henric, great post as usual, just what I was looking for, God Bless you...

0 Likes
2,239 Views
herbert_beck
New Contributor III

Dear Henric,

I like your post to calculate relative ranks in QlikView charts to colour it.

What do you recommend to calculate relative ranks in QlikView-script to calculate an attribute?

The ranking-function, that you are using, is not available in scripts.

Kind regards,

Herbert

0 Likes
2,239 Views

Its possible, but not as straightforward.

Let's say you want to calculate the rank of products regarding sales. Then you need to first load the table containing the order lines just the way you normally would. In a second step, you calculate the aggregated sales numbers. And in a third step, you calculate the rank. The script should be something like:

OrderLines:

Load * From OrderLines ;

Temp:

Load ProductID, Sum(Sales) as TotalSalesPerProduct Resident OrderLines Group By ProductID ;

AggregatedProducts:

Load *, RowNo() as Rank Resident Temp Order By TotalSalesPerProduct Desc ;

Drop Table Temp;

But doing it in a formula in the UI is probably better...

HIC

0 Likes
2,239 Views
herbert_beck
New Contributor III

Dear Henric,

thank you very much for your recommendation. That's the way I do it. But RowNo() is not as perfect as ranking since it isn't covering equal ranks for equal sales.

Kind regards,

Herbert

0 Likes
2,239 Views

True. But then you can, instead of

     RowNo() as Rank

use

     If( TotalSalesPerProduct = Peek(TotalSalesPerProduct ), Peek(Rank), RowNo()) as Rank

HIC

2,239 Views
herbert_beck
New Contributor III

Hi Henric,

thanks, great idea. So

If( TotalSalesPerProduct = Peek(TotalSalesPerProduct ), Peek(Rank), RowNo()) as Rank

is the same as the ranking-function, right?

Kind regards,

Herbert

0 Likes
2,239 Views

Herbert Beck:

It is the same as Rank( <Expr>, 1 ). The second parameter is important here:

Rank function.png

HIC

2,239 Views
satyadev_j
Valued Contributor

Awesome post as usual. Thanks for sharing!

0 Likes
2,239 Views
Not applicable

I'm using Qlikview 11 and can't see to find this option "In QlikView you do this under the expression “Background color” ... some help would be appreciated.

0 Likes
2,239 Views

You need to expand the expression (the small plus sign):Background qv11.png

HIC

2,239 Views
qv_testing
Valued Contributor II

Great Post hic,..

It's very helpful....

0 Likes
2,239 Views
Not applicable

How do I use more than one Dimension? I need to add Code Product and Segment Product in the table.

What do I need change in these expressions?

Tks.

0 Likes
2,239 Views
Partner
Partner

Another good article from HIC.. very useful

0 Likes
2,239 Views
warfollowmy_ver
Contributor III

Hello. I need to choose. I have several stores and catalogs. And in stores and catalogs need calculate ABC through rank and count (rank / count ). How do it?- Aggr (2 or 3 demens.) + rank +set analys. as selectable

0 Likes
2,239 Views
wizardo
Contributor III

Hi,

bit late to the party i guess lol but .....

i can see how it is similar to Pareto, but here you actually select the top % of customers who are sorted by their purchases

but not the sales themselves.

what i mean is that if you say under 0.20 then you take 20% of the number of customers while they are sorted according to their purchases.

so if i have 100 customers total then i will get the 20 customers who have the most purchases. but they can represent much more or much less then 80% of purchases.

so its not exactly a Pareto

hope i made myself clear.

of course i might be wrong

Daniel

2,239 Views
Partner
Partner

Absolutely. That is interesting sort of analysis but in my opinion it is totally different from the ABC analysis.

Most of my projects are in retail industry. In dynamic ABC report we can get answer to such a question for example: How many products give to the company 50% of total sales amount? Set the filter to these products and proceed with futher analysis (on hand values, out of stock and so on). Most common result is something like 500 products from the total number of 8000 active products. That is very different from list of 4000 products with ranking function.

0 Likes
2,239 Views

Just to get the semantics right: Both methods are ABC analyses. Or to put it differently: You can make an ABC analysis by using the pareto method (which customers contribute to the first 80% of my sales) or you can use the ranking method (which customers belong to my better 50%).

Although similar, the methods indeed have different results. The advantage with the pareto method is that it includes the information about how much the customer really has bought. The ranking method does not.

The advantage with the ranking method is that you can define the rank classes as a dimension in your chart. This is not possible if you use the pareto method.

HIC

See also Recipe for a Pareto Analysis

0 Likes
2,239 Views
Partner
Partner

Just to get the semantics right: Both methods are ABC analyses. Or to put it differently: You can make an ABC analysis by using the pareto method (which customers contribute to the first 80% of my sales) or you can usethe ranking method (which customers belong to my better 50%).

I would respectfully disagree on that account. I've just googled term "ABC analysis" and from top results with any explanation details i've get  five pages explicitly citing Pareto principle as a basis for ABC classification. Nowhere I see ranking method as a possible foundation for an ABC analysis.

That's is just what I would expect term of ABC analysis mean.

(I've replaced / to _ in links to circumvent moderation)

  • http:__www.ims-productivity.com_page.cfm_content_ABCPareto-analysis_
  • https:__en.wikipedia.org_wiki_ABC_analysis#cite_note-5
  • https:__www.lokad.com_abc-analysis-(inventory)-definition
  • http:__www.materialsmanagement.info_inventory_abc-inventory-analysis.htm
  • http:__www.apics-redwood.org_articles_art0302BCW.htm
0 Likes
2,239 Views
Partner
Partner
The advantage with the ranking method is that you can define the rank classes as a dimension in your chart. This is not possible if you use the pareto method.

In QlikView we can set selection filter on base of chart expression values, so we can set filter with customers list on base of question "which customers contribute to the first 80% of my sales".

In QlikSense we can not set selection filter on base of chart expression values, only on base of calculated dimension. So in QlikSense we can not use ABC analysis (if we sticking to use a pareto method) for setting such a filter. That a difference in a real case scenario in my view. In standard QlikSense application we can not get a list of customers that contribute to the first 80% of my sales - without getting to Excel on the exported from QlikSense files.

0 Likes
2,239 Views