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
Create a CASE statement
In a data source:
- Edit your data source
- In the upper right, click + ADD A FIELD.
In a report or exploration:
- Edit the report or exploration.
- Select a chart.
- On the right, in the properties panel, click + Add dimension or + Add metric.
- Click + CREATE FIELD.
Then:
- Enter a Name for the new field
- 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.
- Click SAVE or APPLY.
CASE Syntax
CASE
WHEN condition THEN result
(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. If none of the WHEN clause conditions are true, CASE returns the value in the ELSE clause.
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 formulaCASE WHEN Conversions > 100 ...// valid formulaCASE WHEN Dimension1 = Dimension2 ...//invalid formulaCASE 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 ELSE clause specifies a default result. This is returned if none of the WHEN clauses are true. A CASE statement can only have 1 ELSE clause.
The ELSE clause can return the following:
- Dimensions
- Metrics
- Calculated fields
- Literal values
NULL
The ELSE clause is optional. When you omit the ELSE clause, a CASE statement's implicit default result can vary depending on the kind of data you're working with. Generally, an implicit ELSE returns 0 for numeric fields, false for Boolean fields, or the empty string for text fields. The latter may create problems in your data if you are expecting every row to have a value.
For example, say you have a formula like this:
COUNT_DISTINCT(
CASE
WHEN deviceCategory = “Mobile” THEN anonymousUserId
END
)
This formula will return 1 extra count for all the records that don't match, which is caused by counting the empty string result. To avoid this, consider adding an explicit ELSE result.
ELSE NULL
The ELSE clause can return the NULL literal, which is treated differently than the empty string. For example, explicitly returning NULL in the ELSE clause prevents the overcount issue above because COUNT, COUNT_DISTINCT (and other functions that count the number of values, such as AVG and MEDIAN) ignore null values:
COUNT_DISTINCT(
CASE
WHEN deviceCategory = “Mobile” THEN anonymousUserId
ELSE NULL
END
)
More about NULL
As mentioned above, 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.