Use operators and constants with your data to create calculations and find the data you need.
Operators
Use operators in your formulas to combine, compare, and perform mathematical or logical operations with your data.
You can only use each type of operator with certain types, or combinations, of data. However, you can use multiple operators and their compatible data types in a single formula.
Enclose list or line item names in single quotes if they contain:
- a number
- an operator such as a hyphen or asterisk
- the words IF, AND, or OR
Use quotes to ensure that characters in a list or line item name do not affect your formulas and operators.
| Operator | Description | Example |
| + | Adds items. Use with number, date, and time period values. You can add a number to a number, or a number to a date, but not a date to a date. If you add a number type value to a date type value, the date moves forward in time by that number of days. | Cabbages + Carrots + 'Mange-Tout' + 'Oranges and Lemons' |
| - | Subtracts items. Compatible with number and date values. You can subtract a number from a number, a number from a date, or a date from a date. If you subtract a number type value from a date type value, the date moves back in time by that number of days. If you subtract a date from a date, it returns a number, showing the number of days between each date. | Sales - Cost of Goods |
| * | Multiplies items. Compatible with number values. | Price * Units |
| / | Divides items. Compatible with number values. If the divisor is zero, the operator returns zero as the result (the DIVIDE function returns Infinity). | Profit / Sales |
| > | Determines if the left item has a greater value than the right item. Used in formulas that contain IF. Compatible with number, date, time period, or text values. Both items used with this operator must be of the same data type. |
|
| < | Determines if the left item has a smaller value than the right item. Used in formulas that contain IF. Compatible with number, date, time period, or text values. Both items used with this operator must be of the same data type. |
|
| >= | Determines if the left item has a greater or equal value to the right item. Used in formulas that contain IF. Compatible with number, date, time period, or text values. Both items used with this operator must be of the same data type. |
|
| <= | Determines if the left item has a smaller or equal value to the right item. Used in formulas that contain IF. Compatible with number, date, time period, or text values. Both values used with this operator must be of the same data type. |
|
| = | Determines if two items are equal. Used in formulas that contain IF. Can be used to compare number, Boolean, date, time period, or text values. | IF x = 1000 THEN a ELSE b |
| <> | Determines if two items are not equal. Used in formulas that contain IF. Can be used to compare number, Boolean, date, time period, or text values. | IF x <> 1000 THEN a ELSE b |
| & | Concatenates two or more text strings. To ensure characters in the concatenated strings do not affect your formulas, enclose them in quotation marks:
When you concatenate large numbers of, or long, text strings, it can have a negative impact on model performance. Consider the use of alternative data formats to improve performance. |
|
| AND | Tests whether the conditions before and after the AND operator are both met. Compatible with Boolean values. | x > 1000 AND y >1000 |
| NOT | Tests whether the condition after the NOT operator is FALSE. Compatible with Boolean values (in this example, Active? is a Boolean line item). | NOT Active? |
| OR | Tests whether only one of the conditions before and after the OR operator is met. Compatible with Boolean values. | x > 1000 OR y > 1000 |
Constants (TRUE, FALSE, and BLANK)
Use the constants TRUE and FALSE as the result of conditional calculations. TRUE and FALSE resolve to Boolean.
The constant BLANK is the default null value for the list, text, time period, or date data types.
Learn more about:
Calculation engine functionality differences
In Polaris, operators such as >, <, and = return a value of FALSE when used with blank date or time period values.
In the Classic Engine, a non-blank date or time period value is greater than a blank date or time period value.
In Polaris, operators such as >, <, and = are tolerant of small differences to 1E-14. In the Classic Engine, the operators are exact.
For arithmetic operators + and -, Polaris returns a blank date when any date operands are blank. In the Classic Engine, a blank date assumes the minimum possible value.
For arithmetic operator *, Polaris returns 0 when any operands are NaN (Not a Number). The Classic Engine returns NaN.
