Operators

Operators are represented by special characters or keywords; they do not use function call syntax. An operator manipulates any number of data inputs, also called operands, and returns a result.

Arithmetic operators

  • Addition: +
  • Subtraction: -
  • Division: /
  • Multiplication: *

Comparison operators

  • =
  • !=
  • >
  • >=
  • <
  • <=

The operands on both sides of the comparison operators must of the same type: text, number or boolean. 

Logical operators

AND / OR

You can create complex expressions with AND and OR. Use parentheses to group conditions and force order of precedence, if needed.

Boolean expression A AND (Boolean expression B OR Boolean expression C)

IS NULL

Returns true if the input field is empty or null.

Syntax

field IS NULL

Parameters

field—the dimension or metric to evaluate.

Examples

Field contains Formula Results
a Field IS NULL false

 

Field IS NULL true

More on NULL

Unless otherwise specified, all operators return NULL when one of the operands is NULL.

NULL is different from the empty string (""). Whereas trying to use the empty string in arithmetic generally produces an error, using NULL returns the null value:

1+NULL = NULL

1*NULL = NULL

1/NULL = NULL

etc.

On the other hand, while you can concatenate values with the empty string, the result of concatenating any value with NULL is the null value:

CONCAT("A", "", "B") = "AB"

CONCAT("A", NULL, "B") = null

IN

Returns true if the input field contains any of the the listed values. Both the field and list values must be of the same type. IN matches complete values and is case-sensitive. For partial value or case insensitive matching, use REGEXP_MATCH.

Syntax

field IN (value1, value2, ... valueN)

Parameters

field—the dimension or metric to evaluate.
value1,value2,...valueN—the values to look for.
 

Examples

Field contains Field type Formula Results
Bob Text Field IN ('Bob', 'Alice') true
Alice Text Field IN ('Bob', 'Alice') true
Jorge Text Field IN ('Bob', 'Alice') false
20 Number Field IN (10, 20) true
10 Number Field IN (10, 20) true
100 Number Field IN (10, 20) false
Bob Text Field IN ('Bob', 20) error (because Field is text but 20 is a number. To fix this, put "20" in quotes.)

NOT IN

Returns true if the input field does not contain one of the listed values. Both the field and list values must be of the same type. IN matches complete values and is case-sensitive. For partial value or case-insensitive matching, use REGEXP_MATCH.

Syntax

field NOT IN (value1, value2, ...valueN)

Parameters

field—the dimension or metric to evaluate.
value1,value2,...valueN—the values to look for.

Examples

Field contains Field type Formula Results
Jorge Text Field NOT IN ('Bob', 'Alice') true
Alice Text Field NOT IN ('Bob', 'Alicia','Hiroshi') true
Bob Text Field NOT IN ('Bob', 'Alice') false
20 Number Field NOT IN (10, 20) false
10 Number Field NOT IN (10, 20) false
100 Number Field NOT IN (10, 20) true
Bob Text Field NOT IN ('Bob', 20) error*

* Because Field's type is text but 20 is a number. This would work if "20" was delimited by quotes.

 

Was this helpful?
How can we improve it?