Numeric Expressions

What is a numeric expression?

A numeric expression evaluates to data with any of the numeric data types (that is, INTEGER, SHORTINTEGER, DECIMAL, and SHORTDECIMAL). The data in a numeric expression can be any combination of the following:

• Numeric literals

• Numeric variables or formulas

• Dimensions

• OLAP DML functions that yield numeric results

• Date literals, variables, formulas, or functions

In addition, you can join any of these three-part expressions with the arithmetic operators for a more complex numeric expression. You use arithmetic operators in numeric expressions with numeric data, which returns a numeric result. You can also use some arithmetic operators in date expressions with a mix of date and numeric data, to retrieve either a date or numeric result.

OLAP DML arithmetic operators

The following table shows the OLAP DML arithmetic operators. When you use two or more operators in a numeric expression, the expression is evaluated according to standard rules of arithmetic. The column entitled Priority indicates the order in which that operator is evaluated. Operators of the same priority are evaluated from left to right, which are summarized below.

Operator	Operation	Priority
-	Sign reversal	1st
**	Exponentiation	2nd
* and /	Multiplication and division	3rd
+ and -	Addition and subtraction	4th

Note: A comma is required before a negative number that follows another numeric expression, or the minus sign is interpreted as a subtraction operator. For example, intvar,-4.

Mixing numeric data types

You can include INTEGER, SHORTINTEGER, DECIMAL, and SHORTDECIMAL data in the same numeric expression.

The data type of the result is determined according to the following rules.

IF . . .	THEN the result is . . .
all the data in the expression is INTEGER or SHORTINTEGER, and the only operations are addition, subtraction, and multiplication,	INTEGER.
any of the data is DECIMAL or SHORTDECIMAL,	DECIMAL.
you perform any division or exponentiation operations,	DECIMAL.

Automatic conversion of numeric data types

Numbers are converted to different data types according to the following rules.

IF you . . .	THEN  . . .
use a value with the SHORTINTEGER or SHORTDECIMAL data type in an expression,	the value is converted to its long counterpart before using it. Note: See "Boolean Expressions" for information about problems that can occur when you mix SHORTDECIMAL and DECIMAL data types in a comparison expression.
save the results of a calculation as a value with the SHORTINTEGER data type,	NA is stored when the result is outside the range of a SHORTINTEGER (-32768 to 32767).
assign the value of a decimal expression to an object with the INTEGER data type,	the value is rounded before storing or using it. Note: If the decimal value is outside the range of an integer (approximately plus or minus 2 billion), then Express stores NA.
use a decimal value where a value with the INTEGER data type is required,	the value is rounded before storing or using it. Note: If the decimal value is outside the range of an integer (approximately plus or minus 2 billion), then Express stores NA.
assign the value of a decimal expression to a variable with the SHORTDECIMAL data type,	only the first 7 significant digits are stored.

If these conversions are not what you want, then you can use OLAP DML functions to get different results.

Using dimensions in arithmetic expressions

When you use a dimension with a data type of TEXT in a numeric expression, the dimension value is treated as a position (an integer) and is used numerically. The position number is based on the default status list, not on current status. When you use a dimension with a data type of DATE, you must use the CONVERT function when you want the dimension value to be treated as an integer position.

For example, the MONTH dimension in the demo analytic workspace has JAN95 in position 1, FEB95 in position 2, and so on. Even when the list is sorted alphabetically so that APR95 is first, the value APR95 remains in position 4.

Using dates in arithmetic expressions

When you use dates in arithmetic expressions, the result can be numeric or it can be a date. The following table shows the legal operations for dates and the data type of the result.

IF you.... . . .	THEN the result is... . . .
add or subtract a number from a date,	a future or prior date.
subtract a date from a date,	the number of days between them.
add or subtract a number from a time period,	the time period at the appropriate interval in the future or the past, similar to the LEAD or LAG function. The result is NA when there is no dimension value that corresponds to the result. The calculation is made based on the positions of the values in the dimension's default status list.

Limitations of dates in expressions

The following list outlines the ways in which you cannot use dates in expressions.

• You cannot add two dates together.

• You cannot add or subtract a literal value from a dimension value with a date data type. Both operands must actually be dimension values.

  For example, suppose that M1 is a dimension with a data type of MONTH. An error message is returned when you attempt to subtract the literal value AUG97 from the first value in status for the M1 dimension by issuing the first command shown below. However, the number of months between the two values is displayed when, as shown in the second command, you use a QDR to identify AUG97 as a value of M1 and then subtract this dimension value from the first value in status for the M1 dimension.

  Incorrect: show m1 - 'AUG97'

  Correct: show m1 - m1(m1 'AUG97')

• You cannot specify time periods that have different phases or lengths in the same calculation.

  For example, if you tried to subtract a week from a month, then the result would not have any meaning. If you need to compare time periods of different types, then use the IN operator.

Limitations of floating point calculations

All decimal data are converted to floating point format, both for storing and for calculations. In floating point format, a number is represented by means of a mantissa and an exponent. The mantissa and the exponent are stored as binary numbers. The mantissa is a binary fraction which, when multiplied by a number equal to 2 raised to the exponent, produces a number that equals or closely approximates the original decimal number.

Because there is not always an exact binary representation for a fractional decimal number, just as there is not an exact representation for the decimal value of 1/3, fractional parts of decimal numbers cannot always be represented exactly as binary fractions. Arithmetic operations on floating point numbers may result in further approximations, and the inaccuracy will gradually increase with the number of operations. In addition to the approximation factor, the available number of significant digits affects the exactness of the result.

For all of these reasons, a result computed by the TOTAL, AVERAGE, or other aggregation functions on a DECIMAL or SHORTDECIMAL variable may differ in the least significant digits from a result you compute by hand. Because the SHORTDECIMAL data type provides a maximum of only seven significant digits, you will see more of these differences with SHORTDECIMAL data. Therefore, you may want to use the DECIMAL data type for variables that have a fractional decimal component, such as sales, costs, and other variables that contain currency amounts.

Another result of the fact that some fractional decimal numbers cannot be exactly represented by binary fractions is that for such numbers, the DECIMAL data type will offer a different and closer approximation than the SHORTDECIMAL data type, because it has more significant digits. This can lead to problems when SHORTDECIMAL and DECIMAL data types are mixed in a comparison expression. See the topic "Boolean Expressions" for information on how to handle such comparisons.

Controlling errors during calculations

You can control the following types of errors:

• Division by zero -- Dividing a non-NA value by zero normally produces an error. If a divide-by-zero error occurs when you are making a calculation on dimensioned data, then you can end up with partial results. When you use the REPORT or the = command, values are reported or stored as they are calculated, so the division by zero halts the loop before it has gone through all the values.

  If you divide an NA value by zero, then the result is NA; no error occurs. If you want to suppress the divide-by-zero error, then you can change the value of the DIVIDEBYZERO option to YES. This means that the result of any division by zero is NA and no error occurs. This allows the calculation of the other values of a dimensioned expression to continue.

• Root of negative numbers -- It is normally an error to try to take the root of a negative number (which includes raising a number to a non-integer power). If you want to suppress the error message and allow the calculation of roots for non-negative values of the expression to continue, then set the ROOTOFNEGATIVE option to YES.

• Overflow errors -- The DECIMALOVERFLOW option works in a similar manner to DIVIDEBYZERO. It lets you control whether an error is generated when a calculation produces a decimal result larger than it can handle.