​
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 use first_name if it’s not null, otherwise username.

​
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 if number_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.