How to use percentile in summarize.

I want to use percentile for my data set, but it seems like doesn't work...
I'm doing RFM analysis and I need to rank the RFM into 5 category. That's why I need to percentile.
Now:
Costomer ID / Recency
1 / 200
2 / 400
3 / 100
4 / 300

What I want:
Costomer ID / Recency / Percentile
1 / 200 / 0.2
2 / 400 / 0.4
3 / 100 / 0.1
4 / 300 / 0.3

Please help!
Many thanks.
Kazuki

Transformation

Preparation

Accepted answers

Ned

I answered this is a blog post. If you are just looking for quintiles, you can easily divide the percentile for 20.

http://inspiringingenuity.net/2014/10/20/alteryx-percentile-macro/

ned.

All comments

MikeA

Hi Kazuki,

Based on the above, there is a pretty small module you could build to complete this. I copied your above Customer ID and Recency data into an input data tool for this process. Next I added a formula tool and created a new Percentile column using the following expression: [Recency]/1000. Below is a screen shot of the module and the output after the formula tool.

Let us know if you have any questions!
-Mike

k_koebisawa

Hi Mike,

Thank you, Mike. It worked well!
But Percentile was not enough to rank RFM... It's my fault....
Could you tell me how to percentile rank and rank 1 to 5 scale.

I was thinking that first I need a rank percentil column and then make a rank colum based on the a rank percentile colum, but I dont know how to do in Alteryx.

Many thanks.
-Kazuki

Ned

I answered this is a blog post. If you are just looking for quintiles, you can easily divide the percentile for 20.

http://inspiringingenuity.net/2014/10/20/alteryx-percentile-macro/

ned.

k_koebisawa

Hi Ned,

Your tool worked great! Thanks!

-Kazuki

Rewa

Hi Ned,

Your tool works great, thanks!

For my current problem, I would also need to group the data and then allocate percentile ratings within each group. Would it be possible to tweak the tool to help with that?

dantony

Thanks Ned! The percentile macro is great. Is there a way to tweak the macro to calculate percentile within each group?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703