Do you know if your customers are profitable? All of them? Performing Customer Profitability Analysis can answer these questions and give you some amazing, and sometimes counter-intuitive, insights into your customers’ contribution to your bottom line.
This post describes one of the visualizations that you can create once you possess accurate data* on the profitability of your customers: the whale curve.
In a whale curve, customers are ranked by profitability, from highest to lowest, on the X-axis while their accumulated profit is plotted on the Y-axis. The curve that results can, with some imagination, be said to look like a whale coming out of the water. An example of a whale curve chart is shown below.
When you look at this chart, you may notice that the top 200 customers generate the bulk of the profit. You may also notice that the you are losing serious money on the bottom 100 customers and that the customers in the middle are more or less break-even.
Read on to learn how to create a whale curve in QlikView. Even if you’re not interested in creating a whale curve, you might still want to read on to learn more about the rank function and the continuous x-axis.
First off, you can download the complete example qvw by clicking here. The source data for the example can be downloaded by clicking here.
Assuming we have a data model that contains the profit per customer, we can create a whale curve in QlikView using a line chart. In this line chart we first need to create a calculated dimension that ranks each customer according to their profitability.
The expression to use for the calculated dimension is:
=aggr(num(rank(sum(Profit), 4)), Customer)
The way that this expression works is that the total amount of profit is summed and ranked per customer using the sum, rank and aggr functions. The parameter value 4 tells the rank function to increment the rank even when customers have the same amount of profit. The table below illustrates the use of the rank function with and without the parameter value 4.
| Value | Default rank | Parameter 4 rank |
| 5 | 1 | 1 |
| 4 | 2 | 2 |
| 4 | 2 | 3 |
| 3 | 4 | 4 |
| 2 | 5 | 5 |
As the rank function returns a dual value (both a numeric and string value), the num function is used to force the result to be interpreted numerically.
Once the calculated dimension is created, we can create the expression that plots the cumulative profits. This expression is a simple sum statement. Because we want to see the accumulated profit, we select the “Full accumulation” radio button.
The customers need to be sorted from the most profitable to the least profitable customer. Since the calculated dimension that we created returns already ranks the customers in this order, we choose an ascending sort by numeric value.
The example chart shows two reference lines, one showing the actual amount of profit and one showing the maximum amount of profit, i.e. the amount of profit you could have made if you exclude all the customers that you are currently losing money on. We can add these reference lines on the presentation tab.
(please keep in mind that identifying the customers you are currently losing money on is only a start, you will want to find out why you are losing money on these customers, simply getting rid of all of them is not the answer)
The expressions to use for these reference lines are:
Actual profit: =sum(Profit)
Maximum profit: =sum ({$ <Profit={“>0”}>} Profit)
The actual profit is a straightforward sum of the profits. The maximum profit is the same sum, but it adds a set modifier that excludes any negative (or zero) profit amounts.
Next, we need to let QlikView know that the values on the x-axis need to be treated as a continuous range of numbers, not as separate categories. To do this we need to tick the “Continuous” checkbox on the “Axes” tab.
The finishing touch is to properly format the continuous x-axis as an integer on the “Number” tab.
Once this has been done we can click “OK” to close the properties dialog, the whale curve is ready.
You can download the complete example qvw by clicking here. The source data for the example can be downloaded by clicking here.
Should you be interested in more ways to visualize customer profitability then you may want to take a look at my post on decile analysis.
As always, if you have any questions and/or comments, feel free to drop them in the comments section.
* Unfortunately, implementing a costing model such as Activity Based Costing to underpin this type of analysis can be notoriously hard. But that is a subject falls outside of the realm of this QlikView-themed blog, maybe I will cover it on my other blog sometime.