DeepSee MDX Reference
PERCENTILE
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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.
Returned Type
This function returns a number.
Syntax and Details
PERCENTILE(set_expression, optional_numeric_expression, optional_percentile_value)
Where:
The function evaluates the numeric value for each element of the set and returns the value that is at the given percentile.
Tip:
To instead find a value at the percentile across the lowest-level records, use the %KPI function with the sample plugin class %DeepSee.PlugIn.Percentile. See the sample dashboards in the KPIs & Plugins folder in the SAMPLES namespace.
Example
For reference, the following query shows the Patient Count measure for the members of the aged.year level. The ORDER function sorts these members into order by their value of Patient Count so 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.
See Also