Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

abeauchamp
New Contributor

ABC Analysis w. 2 dimensions

Hi

Im attempting to do an ABC analysis by customer  by product in QlikSense.  The end result is to categorize and label each product as one of the following

A – (represents 50% of customer usage)

B – (represents 30% of customer usage)

C – (represents 20% of customer usage)

we have dimensions of

CustomerId

ProductID

and have been using Shipqty as a measure

we've studied Recipe for an ABC Analysis‌, Pareto on 2 dimensions‌ among others....

Our last try was:

=Aggr(if(rangesum(Above(sum(ShipQty)/sum(TOTAL <CustomerID> ShipQty),0,rowno() ))>=0.50,'A',if(rangesum(Above(sum(ShipQty)/sum

(TOTAL <CustomerID> ShipQty),0,rowno()))>=0.30,'B','C')),CustomerID,(ProductID,(=Sum(ShipQty))))

Any help to rework the above calculated dimension to get our end result would be much appreciated

    

below is an example of the end result data

CustomerIDProductIDRankTotal Usage
10683710111255A48
10683710111256A27
10683710111257A27
10683710111258A27
10683710111259C24
10683710111260C24
10683710111261C20
10683710111262A20
10683710111263A12
10683710111264A10
10683710111265B10
10683710111266B10
10683710111267C6
10683710111268A6
10683710111269B6
10683710111270B6
10683710111271C4
10683710111272C4
10683710111273B4
10683710111274C2
10683710111275C1
10683710111276C1
10683710111277C1
10695010111278A12
10695010111279A8
10695010111280B8
10695010111281A7
10695010111282B6
10695010111283A5
10695010111284C4
10695010111285C4
10695010111286A4
10695010111287A4
10695010111288C3
10695010111289C3
10695010111290A3
10695010111291C2
10695010111292C2
10695010111293C1
10695010111294A1
10695010111295A1
10695010111296A1

Thanks

Andrew

10 Replies
MVP
MVP

Re: ABC Analysis w. 2 dimensions

Not sure what is wrong, but it seems that you are missing a little part of the syntax

=Aggr(

If(RangeSum(Above(Sum(ShipQty)/Sum(TOTAL <CustomerID> ShipQty), 0, RowNo())) >= 0.50, 'A',

If(RangeSum(Above(Sum(ShipQty)/Sum(TOTAL <CustomerID> ShipQty), 0, RowNo())) >= 0.30, 'B', 'C')), CustomerID, (ProductID, (=Sum(ShipQty), DESC)))

abeauchamp
New Contributor

Re: ABC Analysis w. 2 dimensions

Thanks Sunny

appreciate your help and added the syntax, but its still not calculating correctly

To help illustrate, we pulled the table below and included customer, product, usage and the Rank it populated

our end game is to have the products representing up to the first 50% of usage(products 10111255-10111258) to  have an A designation

products (10111259-10111262) should have a B designation and the remainder a C

However, in the sample below the top usage items calculated with  A,B and C ratings

 

CustomerIDProductIDRANKTotal Usage
10683710111255A48
10683710111256B27
10683710111257C27
10683710111258C27
10683710111259A24
10683710111260A24
10683710111261A20
10683710111262B20
10683710111263C12
10683710111264A10
10683710111265A10
10683710111266B10
10683710111267A6
10683710111268A6
10683710111269A6
10683710111270B6
10683710111271A4
10683710111272A4
10683710111273A4
10683710111274A2
10683710111275A1
10683710111276A1
10683710111277A1
MVP
MVP

Re: ABC Analysis w. 2 dimensions

Would you be able to share a sample to see the issue?

abeauchamp
New Contributor

Re: ABC Analysis w. 2 dimensions

Sure thing.  but, can you please forward a link on how to attach a sample document in the format you need (still very new to qlik sense)

Highlighted
MVP
MVP

Re: ABC Analysis w. 2 dimensions

abeauchamp
New Contributor

Re: ABC Analysis w. 2 dimensions

Thanks Sunny - please see attached

MVP
MVP

Re: ABC Analysis w. 2 dimensions

This seems like an already aggregated data, right? I was looking for some raw data or the qvw file where you might have been trying this.

juraj_misina
Valued Contributor

Re: ABC Analysis w. 2 dimensions

Hi Andrew,

I did a small adjustment and got results similar to your expectations:

=Aggr(

If(RangeSum(Above(Sum(ShipQty)/Sum(TOTAL <CustomerID> ShipQty), 0, RowNo())) <= 0.50, 'A',

If(RangeSum(Above(Sum(ShipQty)/Sum(TOTAL <CustomerID> ShipQty), 0, RowNo())) <= 0.80, 'B', 'C')), CustomerID, (ProductID, (=Sum(ShipQty), DESC)))

Hope this helps.

Juraj

abeauchamp
New Contributor

Re: ABC Analysis w. 2 dimensions

Thanks Sunny - please see attached.