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