alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

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

Many thanks.
Kazuki
7 REPLIES 7
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
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
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.
7 - Meteor
Hi Ned,

-Kazuki
5 - Atom

Hi Ned,

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

Labels