Rank the rows in the table by percentile using the input column.
RankPercentile(<column>,<direction ”asc” | “desc”>)
column (required) The column used to order the table. The input column can be numbers, dates or text.
direction (optional) The direction to sort the input column. Default is to sort ascending.
NOTE: The percentile is a number between 0 and 1. The last row is always 1 and the first is 0, unless there is only 1 row or there is a tie for the top most value. To display the result as percentages as opposed to numbers, change the column formatting from Automatic to Percent.
RankPercentile([Population by State])
- This will assign the state with the smallest population rank 0, the largest population will be assigned rank 1.
RankPercentile([COUNTY – Count])
More About Window Functions
For most Window Functions, the results are dependent on how the rows within the window are sorted. Many Window Functions will only work properly if the table is uniquely sorted by a column in the same “window” as the Window Function. To ensure that there is a unique sort order without any duplicate values, it is often useful to use multi-column sorting criteria to clearly define how to handle duplicate values.
The complexity of Window Functions makes them a little harder to use, but they are also a very powerful tool.
Cumulative Window Functions
Cumulative Window Functions are aggregate functions that apply to all of the rows up-to and including the current row.
The window that Cumulative Window Functions are applied in must be uniquely sorted for the function to work properly.
Moving Window Functions
Moving Window Functions are aggregate functions which apply to a “window” bracketing the current row. A common moving Window Function is a “moving average”.
The window is specified as a certain number of rows above and below the current row, constrained by the grouping.
The window that Moving Window Functions are applied in must be uniquely sorted for the function to work properly.
Shifting Window Functions
Shifting Window functions either shift the values in a column or repeat a specified value. The functions are useful when calculating month over month data.
The window that Shifting Window Functions are applied in must be uniquely sorted for the function to work properly.
Ranking Window Functions
Ranking Window Functions apply a rank to each row based on the criteria in a row provided to the function.
Ranking functions are not dependent on the sort order of the rows in the window it is applied in.