CASE

Return field values based on conditional expressions.

CASE statements use conditional logic to determine the value of a calculated field.

A common use for CASE is to create new categories or groupings of data. For example, to group selected country values into a Sales Region dimension, you might create a CASE statement like this:

CASE 
    WHEN Country IN ("USA","Canada","Mexico") THEN "North America" 
    WHEN Country IN ("England","France") THEN "Europe" 
    ELSE "Other" 
END
In this article:

Create a CASE statement

In a data source:

  1. Edit your data source
  2. In the upper right, click + ADD A FIELD.

In a report or exploration:

  1. Edit the report or exploration.
  2. Select a chart.
  3. On the right, in the properties panel, click + Add dimension or + Add metric.
  4. Click + CREATE FIELD.

Then:

  1. Enter a Name for the new field
  2. In the Formula box, enter the CASE statement.
    In a data source, click FORMAT FORMULA to automatically layout your formula so that it's easier to read.
  3. Click SAVE or APPLY.

CASE Syntax

CASE
    WHEN condition THEN result
    [WHEN condition THEN result]
    [...]
    [ELSE result]
END

A CASE statement begins with the CASE keyword and ends with the END keyword. In between, you'll have a number sections or "clauses":

  • WHEN: a logical condition you want to evaluate. You can have multiple WHEN clauses in a single CASE statement.
  • THEN: the result to return if the WHEN clause's condition is true. You must have one THEN clause for each WHEN clause in your CASE statement.
  • ELSE. Optional. If none of the WHEN clause conditions are true, CASE returns the value in the ELSE clause, or NULL if no ELSE clause is specified.

WHEN conditions

WHEN conditions evaluate your data and return true if the specified condition is met, or false if it isn't. Valid WHEN conditions can do any of the following:

  • Compare a dimension or metric to a literal value, using a supported operator.
  • Perform a regular expression match on a dimension or metric.
  • Determine whether a dimension or metric is null.
  • Determine whether a dimension or metric contains a value specified in a list.
  • Determine whether a dimension or metric does not contain a value specified in a list.

Limits of WHEN conditions

WHEN must compare to a literal value or to a supported function.

You can't compare dimensions to dimensions or metrics to metrics. In the examples below, the first 2 statements are valid WHEN conditions, while the last 2 are NOT valid:

CASE WHEN Country = "England" ...  // valid formula
CASE WHEN Conversions > 100 ...  // valid formula

CASE WHEN Dimension1 = Dimension2 ... //invalid formula
CASE WHEN Metric1 > Metric2 ... // invalid formula
Supported functions

WHEN conditions can include one or more supported comparison functions. See below.

WHEN conditions can't mix dimensions and metrics.

For example, the following is not allowed:

CASE WHEN Country = "England" AND Conversions > 100 THEN ... // invalid formula

WHEN conditions must contain either a dimension or a metric.

WHEN conditions can't have a literal-only condition. For example, the following is not allowed:

CASE WHEN 1 THEN ... // invalid formula

WHEN conditions can't perform calculations.

It's not possible to do math or use calculated field functions in a WHEN condition. For example, the following is not allowed:

CASE WHEN metric1 + metric2 > 4 THEN ...// invalid formula

You can work around this limitation by first creating a calculated field with the desired formula. Then use that field in the CASE statement. For example,

1) Create a calculated field, MyField, with the following formula:

metric1 + metric2

2) Use MyField in CASE:

CASE WHEN MyField > 4 THEN ...

THEN results

Each WHEN condition must have a matching THEN clause, which specifies the results if that condition is true. If there are multiple WHEN conditions, the CASE statement returns the result for the first true condition.

THEN clauses can return the following:

  • Dimensions
  • Metrics
  • Calculated fields
  • Literal values
  • NULL

Limits of THEN results

THEN clauses can't return an expression or formula.

For example, the following is not allowed:

CASE WHEN Country = "England" THEN metric1 + metric2 END // invalid formula

All of the possible results in a THEN clause must be of the same type.

For example, if the first THEN clause returns text, additional THEN clauses, as well as the ELSE clause must also return text.

ELSE results

The optional ELSE clause specifies a default result. This is returned if none of the WHEN clauses are true. If a CASE statement has no ELSE clause, and none of the WHEN clauses are true, the statement returns NULL. A CASE statement can only have 1 ELSE clause. 

The ELSE clause can return the following:

  • Dimensions
  • Metrics
  • Calculated fields
  • Literal values
  • NULL

More about 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

Comparison operators

You can use the following operators in WHEN clauses:

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

The operands on both sides of the comparison operators must of the same type: text, number or boolean. At least one operand must be a field, the other one can be a text, number, or boolean.

Examples

CASE WHEN Medium != "cpc" THEN "free" ELSE "paid" END

CASE WHEN Time on Page <= 90 THEN 1 ELSE 0 END

Logical operators

You can add AND and OR conditions to WHEN conditions. Use parentheses to group conditions and set order of precedence.

Examples

CASE WHEN Country ISO Code = "US" AND Medium = "cpc" THEN "US - Paid" ELSE "other" END

CASE WHEN REGEXP_MATCH(Video Title, ".*Google Analytics*")  AND is_livestream = TRUE OR Video Length > 120 THEN "GA LIVE or LONG" END

Supported functions

You can use the functions below in WHEN conditions.

Why can't I use other functions in CASE?

WHEN conditions must be a boolean expression. Except for REGEXP_MATCH, calculated field functions return non-boolean results (i.e., numbers, text, dates, geo codes).

IS NULL

Returns true if the input field is empty or null.

This function is only available inside CASE. You can't use IS NULL in standalone calculated fields.

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

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.

This function is only available inside CASE. You can't use IN in standalone calculated fields.

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.

This function is only available inside CASE. You can't use NOT IN in standalone calculated fields.

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.

REGEXP_MATCH

Returns true if the input field matches the regular expression. This function is not limited to CASE statements and can be used in other types of calculated fields.

Learn more about REGEXP_MATCH

Was this helpful?
How can we improve it?

Need more help?

Sign in for additional support options to quickly solve your issue