Evaluates a given expression (or the current measure), across all elements of a set, and returns the value that is at a given percentile level.
This function returns a number.
Syntax and Details
PERCENTILE(set_expression, optional_numeric_expression, optional_percentile_value)
optional_numeric_expression is a numeric-valued expression that the function evaluates for each set element.Typically, this expression has the form[MEASURES].[measure_name]If you do not specify a numeric expression, DeepSee uses the measure used by the current result cell. For example, this might be the measure used on the 0 axis or the measure specified in the WHERE clause, if any. If the query itself does not specify a measure, DeepSee instead uses %COUNT, which counts records in the fact table.
optional_percentile_value is a numeric literal that represents the percentile to find. For example, use30to find the 30th percentile, which is the value that is greater than 30 percent of the other values.If you omit this argument, DeepSee computes the 50th percentile.
The function evaluates the numeric value for each element of the set and returns the value that is at the given percentile.
For reference, the following query shows the
Patient Countmeasure for the members of the
aged.yearlevel. The ORDER function sorts these members into order by their value of
Patient Countso that we can easily compare the later results to this query:
SELECT MEASURES.[%COUNT] ON 0, ORDER(birthd.year.MEMBERS,MEASURES.[%COUNT],BASC) ON 1 FROM patients Patient Count 1 1916 1 2 1921 1 3 1922 1 4 1925 1 5 1941 1 6 1914 2 ... 82 1967 18 83 1969 18 84 1973 18 85 1978 18 86 1979 18 87 1981 18 88 2002 18 89 2009 18 90 1968 19 91 1998 21 92 1991 23 93 2003 23 94 1977 25
Next, the following query shows the 5th percentile value for these members:
SELECT MEASURES.[%COUNT] ON 0, PERCENTILE(birthd.year.MEMBERS,,5) ON 1 FROM patients Patient Count 5 Percentile 1
That is, the 5th percentile consists of birth years that have at most 1 patient.
The following query shows the 95th percentile instead:
SELECT MEASURES.[%COUNT] ON 0, PERCENTILE(birthd.year.MEMBERS,,95) ON 1 FROM patients Patient Count 95 Percentile 18
That is, the 95th percentile consists of birth years that have 18 or fewer patients.
For another example, we use the second argument for PERCENTILE:
SELECT PERCENTILE(birthd.year.MEMBERS,MEASURES.[%COUNT],50) ON 1 FROM patients 50 Percentile 10
For additional, similar examples, see AVG.