# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

 CustomerID ProductID Rank Total Usage 106837 10111255 A 48 106837 10111256 A 27 106837 10111257 A 27 106837 10111258 A 27 106837 10111259 C 24 106837 10111260 C 24 106837 10111261 C 20 106837 10111262 A 20 106837 10111263 A 12 106837 10111264 A 10 106837 10111265 B 10 106837 10111266 B 10 106837 10111267 C 6 106837 10111268 A 6 106837 10111269 B 6 106837 10111270 B 6 106837 10111271 C 4 106837 10111272 C 4 106837 10111273 B 4 106837 10111274 C 2 106837 10111275 C 1 106837 10111276 C 1 106837 10111277 C 1 106950 10111278 A 12 106950 10111279 A 8 106950 10111280 B 8 106950 10111281 A 7 106950 10111282 B 6 106950 10111283 A 5 106950 10111284 C 4 106950 10111285 C 4 106950 10111286 A 4 106950 10111287 A 4 106950 10111288 C 3 106950 10111289 C 3 106950 10111290 A 3 106950 10111291 C 2 106950 10111292 C 2 106950 10111293 C 1 106950 10111294 A 1 106950 10111295 A 1 106950 10111296 A 1

Thanks

Andrew

10 Replies
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)))

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

 CustomerID ProductID RANK Total Usage 106837 10111255 A 48 106837 10111256 B 27 106837 10111257 C 27 106837 10111258 C 27 106837 10111259 A 24 106837 10111260 A 24 106837 10111261 A 20 106837 10111262 B 20 106837 10111263 C 12 106837 10111264 A 10 106837 10111265 A 10 106837 10111266 B 10 106837 10111267 A 6 106837 10111268 A 6 106837 10111269 A 6 106837 10111270 B 6 106837 10111271 A 4 106837 10111272 A 4 106837 10111273 A 4 106837 10111274 A 2 106837 10111275 A 1 106837 10111276 A 1 106837 10111277 A 1
MVP

## Re: ABC Analysis w. 2 dimensions

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

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)

MVP

New Contributor

## Re: ABC Analysis w. 2 dimensions

Thanks Sunny - please see attached

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.

Luminary

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

New Contributor

## Re: ABC Analysis w. 2 dimensions

Thanks Sunny - please see attached.