We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

How to use percentile in summarize.

7 - Meteor
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. 
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.
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!
7 - Meteor
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.
Alteryx Alumni (Retired)
I answered this is a blog post.  If you are just looking for quintiles, you can easily divide the percentile for 20.


7 - Meteor
Hi Ned,

Your tool worked great! Thanks!

5 - Atom

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? 


5 - Atom

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


7 - Meteor

I've adapted to calculate percentile by group.


See the attached workflow (which could be turned into a macro).