Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to use percentile in summarize.

k_koebisawa
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. 
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
7 REPLIES 7
MikeA
Alteryx
Alteryx
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
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.
-Kazuki
Ned
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.

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

ned.
k_koebisawa
7 - Meteor
Hi Ned,

Your tool worked great! Thanks!

-Kazuki
Rewa
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? 

 

dantony
5 - Atom

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

 

SamDrury
7 - Meteor

I've adapted to calculate percentile by group.

 

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

Labels