This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!!
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%.