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.

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!

About The Author

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.

15 Comments

  • 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!
    Help me please!

  • 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.

    Thanks in advance…

  • 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???

    could you please help me on this?

    Many Thanks in advance.

    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.
    GRE Scores instead of sales.

    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.

    thank you for your Reply.

  • 12
    tt
    February 4, 2022 - 09:23 | Permalink

    I have the same trouble, cannot do it in the script in QlilSense

  • Read previous post:
    A Russian QlikView user learns of The Qlik Fix!

    (ok, so not really ;))

    Close