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).