percentile

percentile, function

def nosort process percentile(a: number, p: number): number
def nosort process percentile(a: date,   p: number): date
def nosort process percentile(a: week,   p: number): week
def nosort process percentile(a: month,  p: number): month
def nosort process percentile(a: text,   p: number): text

Returns the value below which a fraction p of the values in a falls. The argument p must be between 0 and 1 (inclusive).

• a: the values to rank within each group.
• p: the percentile as a fraction between 0 and 1.

Examples

table T = with
  [| as A |]
  [| 0 |]
  [| 0 |]
  [| 1 |]
  [| 2 |]

show summary "Percentiles" with
  percentile(T.A; 0.25) as "P25"
  percentile(T.A; 0.50) as "P50"
  percentile(T.A; 0.75) as "P75"
  percentile(T.A; 1.0) as "P100"

This outputs the following summary:

table U = with
  [| as A |]
  [| "A runner" |]
  [| "B mainstream" |]
  [| "C slow mover" |]
  [| "D dead stock" |]

show summary "Text percentiles" with
  percentile(U.A; 0.25) as "P25"
  percentile(U.A; 0.50) as "P50"
  percentile(U.A; 0.75) as "P75"
  percentile(U.A; 1.0) as "P100"

This outputs the following summary:

Remarks

For numbers, the percentile is linearly interpolated between observations when needed. For text, values are ordered lexicographically and interpolation uses the lower neighboring value.