## Decile analysis

#### By Barry Harmsen

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!

#### Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

• 1 October 11, 2010 - 20:20 | Permalink

Thanks for this post! Didn’t know the fractile function and actually it’s exactly what I was looking for about a week ago ;-))

• 2 Arie
September 22, 2011 - 15:06 | Permalink

This is a great example, and l learned a lot from your model -thanks!

I was also quite intrigued by the formulas you used in the creation of your sourcedata file. You also use the function randskew(). I have been using Excel XP and Excel 2007 -is this a new function in Excel 2010, and what does it do?

Regards
Arie

• 4 DR
October 7, 2013 - 10:03 | Permalink

Hi Barry,

Awesome post thanks!

One thing… I have a lot of customers with zero purchases… do you have a way of still getting 10 buckets? In my data 30% of the customers we have “acquired” have spent zero dollars so they are all being grouped in one bucket #10 (e.g. my buckets go 1,2,3,4,5,6,7,10)

Regards

• 5 mona
March 25, 2015 - 12:05 | Permalink

I’ve created a table such this for one product:
as a dimension: Decile (price_sale)
as the expressions: sum(sales), avg(price_sale)
I would like to see this pivot table for all of my products,so when I add another dimension before Decile, it dosen’t work;
for example if I had 10 decile, after adding the new dimension I have 4 decile !
I’m confisued with that!

• 6 Jonathan Tran
June 12, 2015 - 20:14 | Permalink

Hi Barry,

Thanks for this posting and detailed description. I just submitted regarding deciling within Qlikview (https://community.qlik.com/message/796375#796375).

The one thing that seems to be missing within Qlikview is the ability to decile based on Total Volume such that the decile segments account for 10% of the Total Volume as oppose to each decile accounting for 10% of Total Customer Count.

I was curious if you had any thoughts and insights on this type of segmentation.

• 7 Ishan
August 13, 2015 - 10:55 | Permalink

Hi Barry,

Can you please explain more on expression part of the Chart.
Currently I am implementing Decile analysis in Qliksense.
Actally, i want to Analyze my Customers.
I use this expression as Dimension to create 10 segment of Sales.

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

In Expression Part:

1) Sum(Sales)

Now i am getting trouble…how can i segment my Customers in 10 Similar part???

Ishan Bhatt

• 8 Ishan
August 13, 2015 - 12:16 | Permalink

Hi Barry,

I solved my doubt. Actually It was Data problem. It Contains Null Records that’s why we did not get perfect segmentation of Customers.

Thank You.
Ishan Bhatt

• 9 John Irwin
September 28, 2016 - 04:37 | Permalink

Thanks. This helped me figure out doing quartiles.
I just used 1-4, instead of 1-10. And adjusted the .1, .2 to .25, .50 etc.

John

• 10 Chandni Popat
March 21, 2019 - 18:31 | Permalink

What should be the script if I want to implement same in the Load script in Qlikview/Qliksense.

As I want to show each customer with a Decile value in Detail table.

• 11 Patrice Kwamou
October 7, 2019 - 11:27 | Permalink

hello,

i have a Question regarding your post wich is rich an helpful, my Question is as follow:
is there anyway of calculating or create deciles in load script? because the nested Analysis is not working in load script.