Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Request:

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

10 Replies
Not applicable
Author

Hi Jonathan,

Could you please kindly help check my issue, which is also related with decile?

maybe you can help solve my problem, listed below:

Decile or Fractile Issue