Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marcel_olmo
Partner Ambassador
Partner Ambassador

Fractile Doubt Challenge

Hi guys, I have a doubt trying to classify my results.

I have a table of sales by customer, and I'd like to classify them with numbers, to see the quality of my customers, something like :

1 : 0-20%   of the amount of sales

2 : 20-40% of the amount of sales

3 : 40-60% of the amount of sales

4 : 60-80% of the amount of sales

5 : 80-100% of the amount of sales

This would be my desired result :

Cust_idAmountRank
12001
22001
34002
44002
56003
66003
78004
88004
910005
1010005

Any ideas of how to do that? By rank function? fractile?

Many thanks in advance!

Regards, Marcel.

9 Replies
vgutkovsky
Master II
Master II

Marcel, I'm a little confused regarding your requirement. You mentioned that rank #1 would be "0-20% of the amount of sales." Does that mean that you want to order your sales-by-customer from smallest to largest, take the smallest X sales that add up to 20% of the total of all sales, and assign those customers to rank #1?

In your example, you're doing something different? It looks like the target you're comparing against is 1,000, which is the greatest sale for a single customer. So what your example does is, again, sorts sales-by-customer from smallest to largest, but then assigns a rank of #1 to those customers who have sales <=20% of the greatest sale (not the total).

Please clarify.

Vlad

marcel_olmo
Partner Ambassador
Partner Ambassador
Author

Thanks Vlad, the point of this is to do a RFM Analysis, and I need the "scores" of some metrics. One of them is the amount.

I'd like to classify all the amounts by customer by score (5 is the maximum, and 1 is the lower). And I'm trying to do it with percentile, rank... with no success.

Do you know how to do it?

Best regards, Marcel.

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_141855_Pic1.JPG.jpg

SET vRankSize = '20%';

tabCustomers:

LOAD Cust_id,

    Amount

FROM [http://community.qlik.com/thread/141855] (html, codepage is 1252, embedded labels, table is @1);

Left Join

LOAD Max(Amount) as MaxAmount

Resident tabCustomers;

Left Join

LOAD Distinct

    Cust_id,

    Amount,

    Ceil(Amount/MaxAmount,'$(vRankSize)')/'$(vRankSize)' as Rank  

Resident tabCustomers;

DROP Field MaxAmount

hope this helps

regards

Marco

marcel_olmo
Partner Ambassador
Partner Ambassador
Author

Thanks Vlad, the point of this is to do a RFM Analysis, and I need the "scores" of some metrics. One of them is the amount.

I'd like to classify all the amounts by customer by score (5 is the maximum, and 1 is the lower). And I'm trying to do it with percentile, rank... with no success.

Do you know how to do it?

Best regards, Marcel.

marcel_olmo
Partner Ambassador
Partner Ambassador
Author

Thanks Marco for the idea, nice try,

apologies for the misunderstanding, that table was just an example, and the calculation it has to be dynamic, in order to get 5 different groups. I think it is called a quintile, but in Qlikview it only exist the percentile function.

The idea is that each 20% belongs to a group (1,2,3,4,5) and it has to be selected as an expression.

Best Regards Marcel.

vgutkovsky
Master II
Master II

Marcel, I'm still confused. I don't understand if you're trying to rank 20% of your customers or 20% of your volume. I'm going to assume you mean customers. Try this:

5*ceil(rank(-sum(Amount),4,1)/count(distinct total Cust_id),.2)

Edited to add "total" to denominator.

Regards,

Vlad

marcel_olmo
Partner Ambassador
Partner Ambassador
Author

Sorry Vlad if I'm not explaining very well, what I'm trying to do is to classify my customers in 5 groups (as the percentile does). The group 1 is the group of the lowest purchases and the group 5 is the group of the higher purchases. If you see the RFM analysis on the web, you're going to understand it very well. The goal of this is to put an score to a customer.

Regards, Marcel.

vgutkovsky
Master II
Master II

Let me try to ask it another way: will each of your five groups have the same number of customers?

Vlad

Anonymous
Not applicable

Hey,

Here is my theread and attached document for Fractile.

http://qlikcommunity.qliktech.com/docs/DOC-7328