Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Assigning percentiles


Hi -

I am trying to assign percentiles.  I need to assign percentiles by year, group, priority by cost.  I added a record ID, sorted year, group, priority and cost.  I ranked using the multi-row formula [Row-1:Rank]+1.  Used the summarize tool to find the max ranking for each combo and the formula tool to find the percentage [Rank]*100/[Max_Rank]. 


Where I am stuck is assigning the percentiles.  I need to assign  5%,10%,20%, 30%, 40%, 50%, 60%, 70%, 80%, 90%, 100% to each row.  I was playing with the tile tool but wasn't successful.  


Any help or guidance would be greatly appreciated!! 





data example for one group included

Would the Binning tool work in this instance? Or perhaps using a formula tool to round and then bin the results?

Let me know if that helps.
Count all rows with the summarize tool then append it back into your data set. The percentile will be: rowID/total count

The need to assign a straight percentile has changed.  I now need to read through a set of grouped records and assign the percentile based on rank and a +/- where a "bucket" is not easily derived from the percentile.  Meaning, if I need to assign a cost as the 25th %tile and I don't have something that equates to that, I need to take a +/- of 5% to find the next closest dollar amount.  I am not sure how do the a loop while a specific grouping of data is read.


IF value between 25 and <26, chose dollar amount from row with the maximum rank THEN assign 25 as the %tile
ELSE IF value between >= 26 and <31 chose dollar amount from row with the maximum rank THEN assign 25 as the %tile
ELSE IF value between >= 20 and <25 chose dollar amount from row with the maximum rank THEN assign 25 as the %tile
ELSE null


I would sort your data in the order you require, and then use the Multi-Field Binning. Set the number of Tiles to 99 I believe (although it could be 100 I can't remember). And choose one of the following settings depending on what you want to do with the data:


  • Equal Records: Input records are divided into the specified amount of tiles so that each tile is assigned the same amount of records. This is based solely on the record's position in the input file.
  • Equal Intervals: The minimum and maximum values of the tile field are determined. The range is split into equal sized sub-ranges and records are assigned to tiles based on these ranges.


This will assign a percentile to each record. You could then use a formula tool to specify a new field, and if the percentile is say 68% then your formula tool could assign the value 70%.