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

 

QV Bar chart.png

 

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.

 

QS Pivot.png

 

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

 

QS List box.png

 

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

70 Comments
rbecher
MVP
MVP

I see no difference in sorting when just using [Artikelname] as 2nd dimension..

0 Likes
10,766 Views
Anonymous
Not applicable

Ralf you are right! I simplified my issue so much, that it works anyway

What I really tried to achieve is to:

- Sort YEAR numerically

- Sort Month numerically

- Sort Articels by Top-Article in each month.

I hoped that the new Aggr-feature would help, but I failed so far..

2016-12-29 08_50_26-QlikView x64 - Testversion - [C__Users_rva_Desktop_Nordwind_SortableAggr.qvw_].png

0 Likes
10,766 Views
Anonymous
Not applicable

The best way I know to do so, ist something like

=aggr(dual(only(Artikelname),ceil((max(Jahr)*1000000000)+ (max(Monat)* 10000000)+Sum(Sales)*1000)+RowNo(total) ) ,Jahr, Monat,[Artikel-Nr])

I really don't like this approach, because:

- Encoding the Year+Month into the DUAL()-Expression is dangerous when "Sales" is growing. One needs to ensure you have enough Zeroes are set for Year and Monath, so the numeric sorting still works.

- If one article has exactly the same Sales in this Month, the Dual-Value would be eaxtly the same. This is the reason I also encode Rowno() into the Dual-Expression.

If the new AGGR-Sort could help here in some way, it would be great. Maybe someone finds an expression!

2016-12-29 09_32_59-QlikView x64 - Testversion - [C__Users_rva_Desktop_Nordwind_SortableAggr.qvw].png

0 Likes
10,766 Views
rbecher
MVP
MVP

Roland, I still do not understand the issue. In v12.10 SR1 sorting by measure (y-value) in pivot seems to work fine even with three dimensions. Probably better to open a new thread.

0 Likes
10,766 Views
Anonymous
Not applicable
0 Likes
10,860 Views
karthiksrqv
Partner - Creator II
Partner - Creator II

Thanks!

0 Likes
10,860 Views
Anonymous
Not applicable

Gracias Me fue muy útil el ejemplo

0 Likes
10,860 Views
Not applicable

Hi Robert,

I am also facing the same issue. Have you been able to find a solution for this?

Thanks

Aditya

0 Likes
10,860 Views
Not applicable

Hi Henric,

I am also facing same issue as Robert. Here is my expression to create a dimension:

=Aggr(

If(Rangesum(Above(Sum({<open={'Closed'}>}Amount)

    /Sum({<open={'Closed'}>} Total <OTCRange> Amount),1,RowNo()))<=0.8, 'A',

     

     

        If(Rangesum(Above(Sum({<open={'Closed'}>}Amount)

        /Sum({<open={'Closed'}>} Total <OTCRange> Amount),1,RowNo()))<=0.95, 'B',

            'C')),

    (RecordID,(=Sum({<open={'Closed'}>}Amount),Desc))

    )

I have used [OTCRange] as a column and Sum(Amount) as a measure. It gives correct result for a selected OTC range. eg. Suppose [OTCRange] = 'OnTime' , '1 to 5' etc. If i select either of OnTime or '1 to 5' then it gives correct results but if i select  both then it does not work.

Thanks

Aditya

0 Likes
10,860 Views
Not applicable

Below expression worked for me:

=Aggr(

If(Rangesum(Above(Sum({<open={'Closed'}>}Amount)

    /Sum({<open={'Closed'}>} Total <OTCRange> Amount),1,RowNo()))<=0.8, 'A',

    

    

        If(Rangesum(Above(Sum({<open={'Closed'}>}Amount)

        /Sum({<open={'Closed'}>} Total <OTCRange> Amount),1,RowNo()))<=0.95, 'B',

            'C')),

   OTCRange, (RecordID,(=Sum({<open={'Closed'}>}Amount),Desc))

    )

Thanks

Aditya

10,860 Views