Formulas

Use

Within a set, you can use formulas to perform calculations.

Example Example

You can use a formula in a set and instruct the system to subtract a set line from another set line. When you use the set to define report columns, the system calculates a variance column in the report.

End of the example.

A formula consists of operators, operands, absolute values, and parentheses.

You can use formulas in any basic set or data set; you cannot use formulas in a single-dimension or multi-dimension set.

Features

Rules for Creating Formulas

  • When building a formula, you can use the operators in the following table.

Operators in Formulas

Symbol

Operator

+

Addition

-

Subtraction

*

Multiplication

/

Division

****

Exponential value

( )

Parentheses

SIN

Sine

COS

Cosine

TAN

Tangent

SQRT

Square root

TRUNC

Truncation to integer

INT

Truncation to integer

DIV

Quotient of whole number division

MOD

Remainder of whole number division

ABS

Absolute value

EXP

Exponential function (n x )

LOG

Logarithm

ROUND

Rounding

  • You can use the following operands in set formulas:

  • Line numbers in the form ‘nnn’ (for example, ‘001’ for line 001)

  • Value variables (for example, ‘&YEAR’ for the value variable YEAR)

  • Formula variables

Note Note

Beginning with Release 3.0C, the syntax used to enter formula operands has changed. You enter formula operands in the form ‘nnn’ (for example, ‘001’ to enter the set line 001). In releases prior to Release 3.0C, you enter formula operands using angle brackets around the operand (for example, <001> ).

The system automatically converts existing formulas into the new format. You do not therefore need to change your sets that contain formulas.

End of the note.

  • Within a formula you can also use:

  • Absolute values (for example, 10 or 20 )

  • Combinations of absolute values and line numbers (for example,10 * ‘002’ )

  • Boolean Logic

Example Example

You enter the following formula in the second line of a set defined for a report column:

IF ‘001’ > 1000 THEN ‘001’ ELSE 0

If the value in the first report column (set line 001) is greater than 1000, then this value is displayed in the second column; if the value in the first column is less than 1000, the value 0 is displayed in the second column.

For more information, see Introduction to Boolean Logic .

End of the example.

  • To enter line numbers, enter:

  • The complete 3-digit line number, including the leading zero

  • Single quotation marks ' ' around the line number

  • You can enter a formula that refers directly to the result of another formula line.

Example Example

You enter the formula ‘001’ + ‘002’ in set line 003 to add together set lines 001 and 002.

You want to enter a second formula (set line 004) to calculate 50% of the total of set lines 001 and 002. You do so by entering the formula ‘003’ / 2 . The operand ‘003’ in this formula refers to the result of the formula calculated in set line 003.

End of the example.

You enter formulas in a set on the Create/Change Set: Basic Entries screens. For more information about entering a formula in a basic or data set, see Entering Value and Formula Variables in a Set .

Using Formulas in Formula Variables

You can create variables called formula variables , which determine the value for a variable.

You can use the operators in the previous table for a formula variable.

In your formula, you can also use:

  • Variables

The variables you use must use the same table name that you entered for the formula variable.

  • User exits

When you enter variables and user exits in your formula, you must enter single quotation marks ( ' ) around them. When creating a formula for a variable, you use the same syntax rules described in the above "Rules for Creating Formulas" section.

See also:

Creating Formula Variables