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!
15 Comments
Thanks for this post! Didn’t know the fractile function and actually it’s exactly what I was looking for about a week ago ;-))
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
Hi Arie,
The randskew() function creates random data that follows a skewed normal distribution. So instead of a nice bell curve (http://www.daviddarling.info/images/Bell_curve.jpg) you get a distribution like this: http://hsc.csu.edu.au/maths/images/mc_images/DA5Q10AA.gif
I thought that would make the data look a bit more realistic.
This is not a standard function of Excel, it’s a User-Defined function. I believe I used the code that was posted here: http://www.ozgrid.com/forum/showthread.php?t=108175&page=1
Kind regards,
Barry
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
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!
Help me please!
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.
Thanks in advance…
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???
could you please help me on this?
Many Thanks in advance.
Ishan Bhatt
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
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.
GRE Scores instead of sales.
John
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.
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.
thank you for your Reply.
I have the same trouble, cannot do it in the script in QlilSense