Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Calculating the scores of RFM

Feras95p
8 - Asteroid

Dear all,

 

 

I have attached an excel sheet that's calculating the scores of the RFM but I'm facing some problems.

 

What I'm trying to reach is that, for example the scores of R (Recency) it will be calculated by taking all the values in the recency and put them in the ascending order and then divide them into 5 group by taking the lower 20% and give it score 5 and then the next 40% give it 4 and then 60% and give it 3, 80% give it 2 and 100% and give it 1. I did this by using this formula =PERCENTILE.INC(B3:B16,0.2)   the bold is the percentages. But still I'm having some cells which are N/A and some cell are not matching. I thing the problem is something related to the order of the values from lower to higher so in some points it's not reading the values.

 

Any help in that please?

 

 

Thanks in advance for your kind support!

5 REPLIES 5
DavidSta
Alteryx
Alteryx

Hi @Feras95p,

 

this problem is caused by the VLOOKUP in Excel.

Your percentile scores are correct calculated. The VLOOKUP with the TRUE option is an approximate match looking for the lowest value in your column.

 

As 276,2 is your 20th percentile this means all values lower or equal than this should be within the 1st group.

DavidSta_0-1604309394968.png

Source: https://www.statisticshowto.com/

 

Instead of using the 20% of the data you are using the 80% (greater or equal) of your data for the lookup causing the N/A values.

To implement a conditional join like this in Alteryx you can take a look e.g. on this post or you take a look to the "Advanced Join" Macro from the Gallery (find more details on this here).

 

Best regards,

David

atcodedog05
20 - Arcturus

Hi @Feras95p 

 

As @DavidSta  percentile is different from quartile. 20% percentile is 20%th rank 20% quartile is all before 20%th rank.

 

And here is how you can implement percentile in Alteryx 

atcodedog05_0-1604312220142.png

In Summarize tool in numeric section you have a option for percentile.

 

Here is a workflow for the task.

Output:

atcodedog05_1-1604312629166.png

 

Workflow:

atcodedog05_2-1604312643463.png

 

Hope this helps 🙂 And sorry for the late response was away.


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Feras95p
8 - Asteroid

Hi @atcodedog05 ,

 

Thanks for helping ! But I think there is something wrong, like in the monetary it should be the top 20% with scores 5 but in your workflow the opposite the lower 20% is with score 5. Also in the frequency and recency the same problem but in the recency the lower 20% will given 5 and frequency the top 20% will be given the 5 and so on.   

atcodedog05
20 - Arcturus

Hi @Feras95p 

 

Yes There is a default consideration that all values are taken in ascending. So lesser will come in 20 and higher in 80. I would suggest adjust your RFM ranking of 1-5 accordingly in the formula tool.

atcodedog05
20 - Arcturus

Happy to help 🙂 @Feras95p 

 

Cheers and Happy Analyzing 😀

Labels