Decile analysis is a popular segmentation tool. Where a pareto analysis splits the top 20% customers (or products, regions, etc.) from the bottom 80%, decile analysis divides them into equally sized groups of 10%.

The image below shows an example of a decile analysis.

The example shows how a group of 1.000 customers is divided into deciles of 100 customers. Lots of interesting things can be learned from this analysis, amongst other things:

• Your top 10% customers are generating profit that is significantly above average;
• Your top 30% customers are responsible for 80% of your profit;
• You are losing money on your bottom 20% customers (the so-called “bleeders”).

So, how do we create a decile analysis in QlikView? By using the fractile function! This function takes an expression and a fractile and returns the cut-off point for that fractile, for example:

fractile(Profit, 0.25)

returns the amount of profit that cuts of the bottom 25%.

Using this concept, you can create a calculated dimension with nested if’s to create the ten deciles:

=if(Profit <= fractile(TOTAL Profit, 0.1), 10,
if(Profit <= fractile(TOTAL Profit, 0.2), 9,
if(Profit <= fractile(TOTAL Profit, 0.3), 8,
if(Profit <= fractile(TOTAL Profit, 0.4), 7,
if(Profit <= fractile(TOTAL Profit, 0.5), 6,
if(Profit <= fractile(TOTAL Profit, 0.6), 5,
if(Profit <= fractile(TOTAL Profit, 0.7), 4,
if(Profit <= fractile(TOTAL Profit, 0.8), 3,
if(Profit <= fractile(TOTAL Profit, 0.9), 2, 1)))))))))

In the expression above, the TOTAL qualifier is used because the fractile needs to be calculated over the entire dataset, disregarding the chart dimension variables.

Want to see this in action? You can download the example qvw by clicking here, the source data used for this application can be downloaded here. Happy deciling!