Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mvajhuddart
Not applicable

Pareto Analysis

I want to be able to show a Pareto-style analysis of our customers, but at a high-level rather than having to show every customer as we would have to using Henric's solution here: Recipe for a Pareto Analysis – Revisited

I would simply like a metric showing what % of our revenue comes from the Top X% of our customers (say 20%, for example). The only solution I can think of is to rank each customer in the data load, however we want this metric to respond to user selections so don't want to pre-calculate anything.

Any suggestions?

Alan

Tags (2)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Pareto Analysis

This is a lot simpler then I imagined... Try this

='Top 20% of the Customers are worth ' & Num(Sum({<[Customer ID] = {"=Rank(Sum(Revenue)) < (Count(TOTAL DISTINCT [Customer ID])*0.20) + 1"}>}Revenue)/Sum(Revenue), '##.0%') & ' of total revenue'

Capture.PNG

Capture.PNG

7 Replies
vikasmahajan
Not applicable

Re: Pareto Analysis

you can use input box to take % from user store it into variable and use this variable in Top x%

Vikas

sunny_talwar
Not applicable

Re: Pareto Analysis

Would you be able to share some data with the output you are expecting out of it? Also, I hope you are working with QV12.1 or above?

mvajhuddart
Not applicable

Re: Pareto Analysis

This is a (very) simplified version of the dataset:

StoreCustomer IDOrder IDRevenue
London1ABC1£190
London2ABC2£87
London3ABC3£163
London1ABC4£93
London2ABC5£91
London1ABC6£58
London3ABC7£99
London4ABC8£218
London5ABC9£195
London6ABC10£176
London4ABC11£76
London7ABC12£204
London8ABC13£56
London9ABC14£64
New York10ABC15£173
New York10ABC16£117
New York8ABC17£90
New York10ABC18£176
New York8ABC19£58
New York9ABC20£132
New York10ABC21£87
New York8ABC22£140
New York9ABC23£193
New York10ABC24£157

The steps to go through are basically rank the customers in order of their total revenue, then calculate the contribution each on makes to the overall revenue:

Customer IDTotal Revenue% ContributionCumulative Contribution
10£71023.0%23.0%
9£38912.6%35.5%
8£34411.1%46.7%
1£34111.0%57.7%
4£2949.5%67.2%
3£2628.5%75.7%
7£2046.6%82.3%
5£1956.3%88.6%
2£1785.8%94.3%
6£1765.7%100.0%
Grand Total£3,093

From there, I can now say the top 20% of customers (in this case just customers 9 and 10) contribute 35.5% of our revenue.

But, I also want this to react to user selections, so if we filter to just London orders, the data would look like this:

Customer IDTotal Revenue% ContributionCumulative Contribution
1£34119.3%19.3%
4£29416.6%35.9%
3£26214.8%50.7%
7£20411.5%62.2%
5£19511.0%73.2%
2£17810.1%83.3%
6£1769.9%93.2%
9£643.6%96.8%
8£563.2%100.0%
Grand Total£1,770

And, as we have over 300,000 customers, I don't want to show as a table/chart with a record for individual customers, but just a high level metric to say the top 20% of customers are worth Y% of total revenue.

Hope that helps!

Alan

sunny_talwar
Not applicable

Re: Pareto Analysis

This is a lot simpler then I imagined... Try this

='Top 20% of the Customers are worth ' & Num(Sum({<[Customer ID] = {"=Rank(Sum(Revenue)) < (Count(TOTAL DISTINCT [Customer ID])*0.20) + 1"}>}Revenue)/Sum(Revenue), '##.0%') & ' of total revenue'

Capture.PNG

Capture.PNG

mvajhuddart
Not applicable

Re: Pareto Analysis

Works perfectly, thanks Sunny!

gys1992infosys
Not applicable

Re: Pareto Analysis

Hi both,

How to realize below Pareto analysis?

Thanks,

Customer IDTotal Revenue% ContributionCumulative Contribution
10£71023.0%23.0%
9£38912.6%35.5%
8£34411.1%46.7%
1£34111.0%57.7%
4£2949.5%67.2%
3£2628.5%75.7%
7£2046.6%82.3%
5£1956.3%88.6%
2£1785.8%94.3%
6£1765.7%100.0%
Grand Total£3,093
sunny_talwar
Not applicable

Re: Pareto Analysis