10 Replies Latest reply: Jul 31, 2015 8:42 AM by Qianyu Zhuo RSS

    Seeking Guidance on Basing Deciling (Fractile) to be based on Volume Sum NOT Count of Records


      I am looking for guidance on how to create a Calculated Decile Dimension that is based on Total Volume (See attached - first table: DESIRED OUTPUT:  Quartile Based on Volume).   In that table chartable Chart, there are 4 Quartile Segments.   For this view, I am basing the Quartile Segment based on Volume .  Such that each Quartile Segment represents 25% of the total volume.

      The Data Table Chart is based on Raw Data Sheet in the attached excel.

      Issue using Fractile Function:

      Unfortunately within Qlikview, if I use the Fractile function, it bases the Percentile/Quartile on the Customer Count. In other words, each Quartile segments represents 25% of the total Customer Count.   (See attached ACTUAL Ouput: using Fractile Fuction is based on Customer Count).

      Reference Source: http://www.qlikfix.com/2010/10/08/decile-analysis/

      if(Profit <= fractile(TOTAL Volume, 0.75), 'Quartile 4 (100% to 75%)',
      if(Profit <= fractile(TOTAL Volume, 0.50), 'Quartile 3 (75% to 50%)',

      if(Profit <= fractile(TOTAL Volume, 0.25), 'Quartile 3 (50% to 25%)',

      'Quartile 1 (25% to 0%)')))

      I also attempted to use  aggr function with Rank to created the quartile but still no luck. 

      aggr(if(rank(sum(Volume))<0.75*count(total CustID), 'Quartile 4 (100% to 75%)',
      if(rank(sum(Volume))<0.5*count(total CustID),
      'Quartile 3 (75% to 50%)',
      if(rank(sum(Volume))<0.25*count(total CustID),
      'Quartile 3 (50% to 25%)',
      'Quartile 1 (25% to 0%)'))),Customer #)