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)
The formula keywords TRUE and FALSE resolve to the corresponding Boolean values.
The constant BLANK is the default empty value for the list, text, time period, or date data types.
Note: Please refer to ISBLANK if you are looking for a way to test whether a value is equal to blank.
Learn more about:
Calculation engine functionality differences
In Polaris, when comparing text values, blank is equal to itself and less than every other text value. Otherwise, comparisons with blank always return FALSE.
In the Classic Engine, blank is always equal to itself. When comparing dates or text values, blank is less than every other value; otherwise inequalities with blank always return FALSE.
In Polaris, operators such as >, <, and = are tolerant of small differences up to a threshold of 1E-14. In the Classic Engine, the operators are exact.
When the operators + and - are applied to date values, Polaris returns blank when either operand is blank.
When either operand of the * operator is NaN, Polaris returns 0, while Classic returns NaN.
