23 Replies Latest reply: Jun 1, 2017 9:54 AM by Sunny Talwar RSS

    Create Potential Buckets as Calculated Dimension

    Phalgun Parvathaneni

      Hello all,

       

      I have a rather complicated set of rules to create a bucket which classifies Vendors as 'High','Medium' or 'Low'. I have the data as follows:

       

             

      VendorSalesTypeType1No.Of AlternatesScore1Score2Score3
      T531.88CInter357131
      L405CTrade252131
      F110.5CInter47131
      V5101.6NCInter1778131
      U45.35NCInter84131
      U30.43NCInter84131
      L196CInter425131
      F91.77CInter107131
      D103.5NCInter83131
      S594NCInter53131
      I2470.29CInter6121
      N45NCInter121131
      Q120NCInter379131
      X439.84NCInter4111
      H78.72CInter495131
      H7924.44NCInter38131
      M430.5CInter15131
      L2919.24NCInter217131
      L2229.74NCInter217131
      C231777CInter99131
      A249.24NCInter293131
      F96CInter22131
      E1831.8NCTrade10121
      G63.42NCTrade9121
      T86.11CInter357131
      T142.04999CInter357131
      H197.22CInter318131
      H18.38CInter318131
      H33CInter318131
      D20.96CInter436131
      D18.9CInter436131
      B13.85NCInter253131
      B110.3NCInter253131
      B1.52NCInter2111
      B163.96NCInter253131
      B2NCInter1111
      B0.98NCInter2111
      B65.78NCTrade253131
      W82746NCTrade383131
      J48.99NCTrade67131
      P78.61CInter416131
      P82.83CInter416131
      P95.42CInter416131
      P36CInter416131
      O496.80002NCInter83131
      O900NCInter83131
      K91037.24CInter1205131
      R160NCInter1778131
      I1750CInter1778131
      I1950CInter1778131

       

      Now what I'd like to do is as follows:

      1.Perform a Pareto analysis on the above data and classify the Vendors into 3 classes: Top 80%,80-95% and Bottom 5%.

      2.Based on the Vendor's Pareto class, I'd like to add another Score criteria, let's say Score 4. Score 4 should be allotted as follows:

        (a).If the Vendor falls in Top 80%, allot Score4 as 1.

        (b).If the Vendor falls in 80-95%, allot Score4 as 2.

        (c).If the Vendor falls in Bottom 5%, allot Score4 as 3.

      3.After completing the Score4 column, now sum up all the Score Columns, Score1 through Score4. Let's call it Total Score.

      4.If Total Score <=7, Bucket is 'Low'. If  7<Total Score<=10, Bucket is 'Medium'. If Total Score>10, Bucket is 'High'.

       

      Now, there are some exceptions to the above rules as well:

      (a). If Type='C', the Bucket should automatically be 'Low'.

      (b).If Type1='Trade', the Bucket should automatically be 'Low'.

      (c).If No.Of Alternates < 3, the Bucket should automatically be 'Low'.

      In short if any one or more of the above criteria are met for the Vendors, the Bucket should be 'Low'.

       

      Added to my complications, all this should be implemented in the front end, as a Calculated Dimension. This is way out of my little knowledge of Qlik Sense and I badly need help to implement this. I'd be grateful to anyone who'd hep me out in this! TIA!

       

      stalwar1 Help me out kind sir!