Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
8 - Asteroid

There was a recent question on the Alteryx forum: How to use the percentile in summarize.  The question misunderstands the percentile function in the summarize and is looking for something slightly different, although with similar math.  So what does the percentile in the Summarize tool do?  From the help:

Percentile: Calculates the specified percentile value for the group. The percentile is calculated by sorting the data and returning the row value relative to the specified percentile and its position in the sorted array – the largest value is the 100th percentile, lowest value is the 0 percentile, median is the 50th percentile, the 25th percentile is the value in the middle of the median and minimum, etc.

 

So in short, in this case, you specify the percentile you want, like the 80th percentile, and it returns you the value that 80% of the other values are below.

 

What this commentator is asking for is the reverse.  They have a column of values and want to know the percentile of each value. Mostly this is a matter of sorting and then numbering all the records appropriately.  I built a quick macro to make this process simple – if you want to skip to the answer, you can download it here.

 

Technical Details

 

This is a pretty simple macro.  Since calculating the percentile involves sorting the records by the field in question, I added some complexity to avoid changing the resulting sort order from the macro.  Before sorting I add a recordID to capture the original sort order so that I can re-sort of it at the end.  I also add a __ to the beginning of all the fields I use internally.  The purpose of that is to make it less likely that I will stomp on a consumer’s fields in the output.

 

There are a few subtleties of the algorithm.  Percentile is defined as the percentage of records not including the current one that are lower than the current record.  This means that the lowest record has a percentile of 0 and the highest 100.  For each record, the basic formula is:

 

[ZeroBasedRecordID]/([RecordCount]-1)

 

The reason for the -1 is that the current record does not count in the total.  If the current record counted in the total, then the highest value would no longer have a percentile of 100.

 

The other complexity is that values that are the same should result in the same percentile.  For this reason I apply the formula in a multi-row formula so I can look at the previous row and, if the same, assign the same percentile.

 

This is a great example of how flexible the macro system is in Alteryx.  Almost any function that isn’t there can easily be added as a macro.  Once again, if you just want to use the macro, you can download it here.

 

Thanks for reading,

Ned.

 

 

 


Comments
5 - Atom

I am trying to use the Percentile function in the Summarize tool and I would like it to return the 75th percentile of units for each category. However, I cannot seem to figure out where I am suppose to specify 75 as the Percentile that I would like. What am I missing here? Thanks!

Patricia

Alteryx Alumni (Retired)

Patricia,

When you click on the field where you want to specify the percentile, the place to put the value is at the bottom of the Configuration window.

 

Percentile.png

 

This configuration will give me the value for the 50th percentile for each category.

5 - Atom

Very useful...thanks!