Numeric Expressions (MDX)

This section describes how to create and use numeric expressions in InterSystems MDX.

Details

In InterSystems MDX, a numeric expression can have any of the following forms:

A numeric literal. For example: 37
The literal cannot start with a decimal point; that is, you must include a leading 0 with any fractional values. For example, 0.1 is valid is valid, but .1 is not valid.
A percentage literal. For example: 10%
There must be no space between the number and the percent sign.
An expression that refers to a numeric-valued measure, such as MEASURES.[%COUNT]
An expression that uses an MDX function that returns a numeric value, for example: AVG(aged.age, MEASURES.[test score])
Many MDX functions return numeric values, including AVG, MAX, COUNT, and others. Also, the IIF function can return numeric values; this function evaluates a condition and returns one of two values, depending on the condition.
An expression that uses mathematical operators to combine numeric expressions. For example: MEASURES.[%COUNT] / 100
The system supports the standard mathematical operators: + (addition), - (subtraction), / (division), and * (multiplication). It also supports the standard unary operators: + (positive) and - (negative).
You can use parentheses to control precedence.
In the expression, if any value is null, the expression evaluates to null.
If you divide a value by 0, the system treats the result as null.

Tip:

The MDX function IIF can be useful in such expressions.
A member expression, such as [gend].[h1].[gender].[female]
Note that the value of a member expression depends upon the measure that is currently in use. By default, this expression evaluates to the number of records that belong this member. In contrast, if a specific measure is in use, this expression evaluates to the aggregate value of that measure across those records.
The MDX identifier for a dimension, such as [gend]
Note that the value of this expression depends upon the measure that is currently in use. By default, this expression evaluates to the number of records in the cube. In contrast, if a specific measure is in use, this expression evaluates to the aggregate value of that measure across all records in the cube.
A reference to a pivot variable that contains a numeric value. To refer to a pivot variable, use the following syntax:
```
$VARIABLE.variablename
```
Where variablename is the logical variable name. Do not enclose this expression with square brackets. This syntax is not case-sensitive; nor is the pivot variable name.
For information on defining pivot variables, see Defining and Using Pivot Variables.

Uses

You can use numeric expressions in the following ways:

As a numeric argument to many MDX functions. For example:
```
AVG(diagd.MEMBERS, MEASURES.[%COUNT])
```
As an element of a set.
As the definition of a calculated member (in this case, a measure).

Examples

This section shows examples of some of the less common kinds of numeric expressions. The first example shows that a member expression has a numeric value:

SELECT gend.h1.gender.female ON 0 FROM patients
 
                                    Female
                                        526

The next example is a variation of the preceding:

SELECT gend.h1.gender.female+100 ON 0 FROM patients
 
                                Expression
                                        626

As noted earlier, the value of a member expression depends upon the measure that is in use:

SELECT gend.h1.gender.female ON 0 FROM patients WHERE MEASURES.[avg age]
 
                                    Female
                                      37.23

Similarly:

SELECT gend.h1.gender.female+500 ON 0 FROM patients WHERE MEASURES.[avg age]
 
                                Expression
                                     537.23

The next example shows the numeric value of a dimension:

SELECT allerd ON 0 FROM patients
 
 
                                      1,000

As noted earlier, the value of such an expression depends upon the measure that is in use:

SELECT allerd ON 0 FROM patients WHERE MEASURES.[avg allergy count]
 
 
                                       0.99

String Expressions (MDX)