Ranking and percentile functions
Place one value among many.
Syntax
Write the function's name with the value or the list first, as each entry below shows. The list is written in square brackets or held in a variable.
RANK(number, ref, [order])
RANK.EQ(number, ref, [order])
RANK.AVG(number, ref, [order])
PERCENTRANK(array, x, [significance])
PERCENTRANK.INC(array, x, [significance])
PERCENTRANK.EXC(array, x, [significance])
PERCENTILE.EXC(array, k)
QUARTILE(array, quart)
QUARTILE.EXC(array, quart)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
RANK
Required: Yesnumber
Required: Yesref
Required: NoorderRANK.EQ
Required: Yesnumber
Required: Yesref
Required: NoorderRANK.AVG
Required: Yesnumber
Required: Yesref
Required: NoorderPERCENTRANK
Required: Yesarray
Required: Yesx
Required: NosignificancePERCENTRANK.INC
Required: Yesarray
Required: Yesx
Required: NosignificancePERCENTRANK.EXC
Required: Yesarray
Required: Yesx
Required: NosignificancePERCENTILE.EXC
Required: Yesarray
Required: YeskQUARTILE
Required: Yesarray
Required: YesquartQUARTILE.EXC
Required: Yesarray
Required: Yesquart
Examples
Five stores' weekly sales held once: where a store of 515 stands from the top, from the bottom, and how a tie is shared.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
RANK | whole number | The place of the value in the list. Equal values share the place | RANK(5, [1, 5, 9]) |
RANK.EQ | whole number | The same as RANK | RANK.EQ(5, [1, 5, 9]) |
RANK.AVG | decimal | The place of the value, where equal values share the average of the places they cover | RANK.AVG(5, [1, 5, 5, 9]) |
PERCENTRANK | decimal | The share of the list below the value, from 0 to 1 | PERCENTRANK([1, 2, 3, 4, 5], 4) |
PERCENTRANK.INC | decimal | The same as PERCENTRANK | PERCENTRANK.INC([1, 2, 3, 4, 5], 4) |
PERCENTRANK.EXC | decimal | The share of the list below the value, counted as if one more value sat at each end, so never 0 or 1 | PERCENTRANK.EXC([1, 2, 3, 4, 5], 4) |
PERCENTILE.EXC | decimal | The value at that fraction of the list, counted as if one more value sat at each end | PERCENTILE.EXC([1, 2, 3, 4, 5], 0.5) |
QUARTILE | decimal | The quarter point of the list, counting the ends as included. The same as QUARTILE.INC | QUARTILE([1, 2, 3, 4, 5], 3) |
QUARTILE.EXC | decimal | The quarter point of the list, counted as if one more value sat at each end | QUARTILE.EXC([1, 2, 3, 4, 5], 1) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-2001 | An argument is not a number or a list of numbers, a value is missing from the list for RANK, or a value or quarter is out of range | Pass numbers in range, and a value that is in the list |
HTQL-8004 | PERCENTILE.EXC was given a fraction not above 0 and below 1, or a list too short to place it; QUARTILE.EXC was given a list too short | Pass a fraction strictly between 0 and 1 and enough values |
Limitations
RANK,RANK.EQandRANK.AVGrefuse a value that is not in the list.PERCENTRANKandPERCENTRANK.INCrefuse a value outside the range of the list;PERCENTRANK.EXCrefuses a value that is not strictly inside it.QUARTILEneeds a quarter from 0 to 4 andQUARTILE.EXCfrom 1 to 3;QUARTILE.EXCneeds at least 4 values for quarters 1 and 3.PERCENTILE.EXCneeds a fraction strictly between 0 and 1.- The list holds numbers only; an empty value or text in it is refused.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use RANK.AVG when ties should not look like a clear win.
- Use QUARTILE or PERCENTILE.EXC for a service promise such as "nine in ten orders arrive within".
- Use PERCENTRANK to show a customer or a store where it stands, as a share of its peers.