CASE (Conditional Logic)
- Syntax:
case(when(yesno_arg, value_if_yes), ...)
- About: Executes a series of conditions and returns a corresponding value for the first true condition.
- Example: To categorize customers based on MRR,
case(when(MRR > 1000, "Enterprise"), when(MRR > 500, "Mid-market"), "SMB")
labels customers as “Enterprise” or “Mid-market” based on their MRR, or “SMB” by default.
COALESCE (First Non-Null Value)
- Syntax:
coalesce(value_1, value_2, ...)
- About: Returns the first non-null value from a list of columns.
- Example: To ensure a display name is always shown,
coalesce(first_name, username)
would usefirst_name
if it’s not null, otherwiseusername
.
IF (Conditional Statement)
- Syntax:
if(yesno_expression, value_if_yes, value_if_no)
- About: Evaluates an expression and returns one value if true, and another if false.
- Example: To check if a subscription is active,
if(status = "Active", "Yes", "No")
returns “Yes” if the status is active, “No” otherwise.
IS_NULL (Null Check)
- Syntax:
is_null(value)
- About: Tests if a value is null.
- Example: To verify if a customer’s last payment date is recorded,
is_null(last_payment_at)
would return Yes if it’s null, indicating a missing payment date.
IFERROR (Error Handling)
- Syntax:
iferror(value, value_if_error)
- About: Returns the value unless it’s an error, in which case it returns an alternative.
- Example: When calculating an average,
iferror(total_payments / number_of_payments, 0)
would prevent division by zero errors by returning 0 ifnumber_of_payments
is zero.
IFNA (Handle #N/A Errors)
- Syntax:
ifna(value, value_if_na)
- About: Evaluates a value and returns it unless it’s an #N/A error, in which case it returns an alternative.
- Example: To handle missing MRR values,
ifna(MRR, 0)
could replace #N/A with 0 in revenue calculations.
IFS (Multiple Conditions)
- Syntax:
ifs(condition1, value1, condition2, value2, ...)
- About: Tests multiple conditions and returns the corresponding value for the first true condition.
- Example: To classify subscriptions by tenure,
ifs(diff_months(activated_at, today()) > 12, "Yearly", diff_months(activated_at, today()) > 1, "Monthly", "New")
categorizes them as “Yearly,” “Monthly,” or “New” based on how long ago they were activated.
SWITCH (Match Cases)
- Syntax:
switch(expression, case1, value1, [default or case2, value2, ...])
- About: Compares an expression against a list of cases and returns the corresponding value for the first match.
- Example: To translate a subscription status code to a text status,
switch(status_code, 1, "Active", 2, "Paused", 3, "Cancelled", "Unknown")
switch(status_code, 1, "Active", 2, "Paused", 3, "Cancelled", "Unknown")
would return “Active,” “Paused,” or “Cancelled” based on the code, or “Unknown” if there’s no match.