How to use percentile in summarize.

[k_koebisawa]

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

[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 · Accepted solution]

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?

[SamDrury]

I've adapted to calculate percentile by group.

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