InterSystems IRIS Data Platform 2019.2  /  InterSystems MDX Reference  /  MDX Functions

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

Returns the value closest to the median value, for a given expression (or of the current measure), across all elements of a set that have a non-null value for that expression.
Returned Type
This function returns a number.
Syntax and Details
MEDIAN(set_expression, optional_numeric_expression)
The function evaluates the numeric value for each element of the set, ignores any set elements for which this value is null, and finds the value that is closest to the median for the remaining elements.
To instead find the median value across the lowest-level records, use the %KPI function with the sample plug-in class %DeepSee.PlugIn.Median. To see examples, download and set up the samples as described in “Accessing the Samples Shown in This Book,” in the preface; then display the User Portal in the namespace where you downloaded the samples and look at the sample dashboards in the KPIs & Plug-ins folder.
First, the following query shows values of three measures for the members of the aged.decade level:
SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0, 
birthd.decade.MEMBERS ON 1 FROM patients
                     Patient Count    Encounter Count     Avg Test Score
 1 1910s                         80              5,359              75.17
 2 1920s                        227             12,910              74.20
 3 1930s                        567             33,211              74.67
 4 1940s                        724             38,420              73.39
 5 1950s                      1,079             46,883              73.72
 6 1960s                      1,475             57,814              74.16
 7 1970s                      1,549             49,794              74.35
 8 1980s                      1,333             35,919              74.13
 9 1990s                      1,426             29,219              74.79
10 2000s                      1,406             20,072              74.95
11 2010s                        134              1,346              73.55
Next, the following query shows the average values for these measures for the members of this level:
SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0, 
MEDIAN(birthd.decade.MEMBERS) ON 1 FROM patients
                     Patient Count    Encounter Count     Avg Test Score
MEDIAN                        1,079             33,211              74.20
Here, each value is the median of the values in a column in the preceding query. For example, the Patient Count value is the median value of the Patient Count values in the preceding query.
For another example, we use the second argument for MEDIAN:
For additional, similar examples, see AVG.
See Also

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-18 06:48:41