Using InterSystems MDX
Adding Summaries
|
|
This chapter describes how to add summaries (such as averages and totalsxt) to your MDX queries. It discusses the following topics:
MDX includes functions that summarize a given value, across a given set. For each function, the arguments are a set and an optional numeric expression (such as a reference to a measure). The system evaluates the expression for each member of the set and then returns a single value. If no numeric expression is given, the system instead evaluates the measure used in the query (possibly %COUNT).
The functions are as follows:
-
SUM, which returns the sum of the values.
-
AVG, which returns the average value. This function ignores members for which the expression is null.
-
MAX, which returns the maximum value.
-
MIN, which returns the minimum value.
-
MEDIAN, which returns the value from the set that is closest to the median value.
-
STDDEV, which returns the standard deviation of the values.
-
STDDEVP, which returns the population standard deviation of the values.
-
VAR, which returns the variance of the values.
-
VARP, which returns the population variance of the values.
SELECT MAX(diagd.diagnoses.MEMBERS,MEASURES.[%COUNT]) ON 0 FROM demomdx
MAX
828
This query shows the maximum value of the
%COUNT measure for the members of the
Diagnoses level.
For another example, use the same function without specifying its second argument. In this case, the query displays the
%COUNT measure as a column:
SELECT MEASURES.[%COUNT] ON 0, MAX(diagd.diagnoses.MEMBERS) ON 1 FROM demomdx
%COUNT
MAX 828
For another example, use the same function without specifying any measure in the query at all:
SELECT MAX(diagd.diagnoses.MEMBERS) ON 0 FROM demomdx
MAX
828
In this case, the system uses
%COUNT.
More typically, rather than displaying the summary value by itself, you include it in a query that shows all the values of the set. This process is analogous to adding a summary line (as a row or column) in a spreadsheet.
The following example shows the
%COUNT measure for each diagnosis, followed by the maximum value for this measure across this set:
SELECT MEASURES.[%COUNT] ON 0,
{diagd.diagnoses.MEMBERS, MAX(diagd.diagnoses.MEMBERS,MEASURES.[%COUNT])} ON 1 FROM demomdx
%COUNT
1 None 828
2 asthma 90
3 CHD 37
4 diabetes 45
5 osteoporosis 22
6 MAX 828
Notice that the system first computes the
%COUNT measure for each member using the aggregation method defined for that measure. In this case, the patients are counted. The
asthma member, for example, has a total
%COUNT value of 90. The MAX function then obtains the largest value for this measure, across the set of diagnoses.
SELECT {gend.gender.MEMBERS, AVG(gend.gender.MEMBERS,MEASURES.[%COUNT])} ON 0,
MEASURES.[%COUNT] ON 1 FROM demomdx
Female Male AVG
%COUNT 488 512 500
WITH SET genders AS 'gend.gender.MEMBERS'
SELECT {genders, AVG(genders,MEASURES.[%COUNT])} ON 0, MEASURES.[%COUNT] ON 1 FROM demomdx
Content Date/Time: 2019-02-22 00:52:40