Operators

To create calculated fields and formulas, you need to understand the operators supported by Tableau. This section discusses the basic operators that are available and the order (precedence) of operations.

+ (addition)

This means addition when applied to numbers and concatenation when applied to strings. When applied to dates, it can be used to add a number of days to a date. For example,

7 + 3

Profit + Sales

'abc' + 'def' = 'abcdef'

#April 15, 2004# + 15 = #April 30, 2004#

– (subtraction)

This means subtraction when applied to numbers and negation if applied to an expression. When applied to dates, it can be used to subtract a number of days from a date. Hence it can also be used to calculate the difference in days between two dates. For example,

7 - 3

Profit - Sales

-(7+3) = -10

#April 16, 2004# - 15 = #April 1, 2004#

#April 15, 2004# - #April 8, 2004# = 7

* (multiplication)

This means numeric multiplication. For example, 5 * 4 = 20.

/ (division)

This means numeric division. For example, 20 / 4 = 5.

% (modulo)

This calculates a numeric remainder. For example, 5% 4 = 1.

= =, =, >, <, >=, <=, !=, <>(comparisons)

These are the basic comparison operators that can be used in expressions. Their meanings are as follows: = = or =(equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), != and <> (not equal to).

Each operator compares two numbers, dates, or strings and returns a boolean (TRUE or FALSE). Booleans themselves, however, cannot be compared using these operators. For example, TRUE=TRUE is not a valid expression. To compare booleans in this way, use the logical operators AND and OR. For example, TRUE AND TRUE is a valid expression.

^ (power)

This symbol is equivalent to the POWER function. It raises a number to the specified power.

For example:

6^3 = 216

AND

This is a logical operator. An expression or a boolean must appear on either side of it. For example,

IIF(Profit =100 AND Sales =1000, "High", "Low")

If both expressions are TRUE (i.e., not FALSE and not UNKNOWN), then the result is TRUE. If either expression is UNKNOWN, then the result is UNKNOWN. In all other cases, the result is FALSE.

If you create a calculation in which the result of an AND comparison is displayed on a worksheet, Tableau displays TRUE and FALSE. If you would like to change this, use the Format area in the format dialog.

OR

This is a logical operator. An expression or a boolean must appear on either side of it. For example,

IIF(Profit =100 OR Sales =1000, "High", "Low")

If either expression is TRUE, then the result is TRUE. If both expressions are FALSE, then the result is FALSE. If both expressions are UNKNOWN, then the result is UNKNOWN.

If you create a calculation in which the result of an OR comparison is displayed on a worksheet, Tableau displays TRUE and FALSE. If you would like to change this, use the Format area in the format dialog. The OR operator employs "short circuit evaluation." This means that if the first expression is evaluated to be TRUE, then the second expression is not evaluated at all. This can be helpful if the second expression results in an error when the first expression is TRUE, because the second expression in this case is never evaluated.

NOT

This is a logical operator. It can be used to negate another boolean or an expression. For example,

IIF(NOT(Sales = Profit),"Not Equal","Equal")

Precedence

All operators are evaluated in a specific order. For example, 2*1+2 is equal to 4 and not equal to 6. The reason is that the * operator is always evaluated before the + operator.

The following table shows the order in which operators are evaluated. The first line has the highest precedence. Operators on the same line have the same precedence. If two operators have the same precedence they are evaluated from left to right in the formula.

Precedence Operator
1 – (negate)
2 ^ (power)
3 *, /, %
4 +, –
5 ==, >, <, >=, <=, !=
6 NOT
7 AND
8 OR

Parentheses can be used as needed. Operators that appear within parentheses are evaluated before those outside of parentheses, starting from the innermost parentheses and moving outward. For example, (1+ (2*2+1)*(3*6/3)) = 31.