10 Replies Latest reply: Dec 23, 2017 10:44 PM by Sunny Talwar RSS

    ABC Analysis w. 2 dimensions

    Andrew Beauchamp

      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