InterSystems MDX Reference
PERCENTILERANK


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.
This function returns a
number.
PERCENTILERANK(set_expression, numeric_expression, comparison_value)



comparison_value is a numeric literal that represents the value against which the system compares the numeric expression for all set members.
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.
Content Date/Time: 20190219 22:37:06