docs.intersystems.com
Home / InterSystems MDX Reference / MDX Functions / PERCENTILERANK

InterSystems MDX Reference
PERCENTILERANK
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


For a given numeric value, this function evaluates a given expression (or the current measure), across all elements of a set, and returns the percentile rank of that expression — the percentage of values that are the same or lower.
Returned Type
This function returns a number.
Syntax and Details
PERCENTILERANK(set_expression, numeric_expression, comparison_value)
Where:
The function evaluates numeric_expression for each element of the set, compares that value to comparison_value, and returns the percentile rank of the comparison value.
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
 7 1918                                   2
 8 1920                                   3
 9 1927                                   3
10 1931                                   3
11 1934                                   3
12 1926                                   4
13 1932                                   4
14 1949                                   4
15 1955                                   4
16 1956                                   4
17 1928                                   5
18 1930                                   5
19 1937                                   5
20 1938                                   5
21 1966                                   5
22 1929                                   6
23 1940                                   6
24 1952                                   6
25 1939                                   7
...
93 2003                                  23
94 1977                                  25
Next, the following query shows the percentile rank of a member that has 5 patients:
SELECT PERCENTILERANK(birthd.year.MEMBERS,MEASURES.[%Count],5) ON 1 FROM patients
 
 
Rank of 5                             19.68
That is, if you consider birth years that have 5 patients or fewer, these birth years constitute 19.68% of the set of years.
See Also


Previous section           Next section
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-04-10 14:45:56