Calculate measures and filters

When you're building a Scuba query, whenever you select a property, you can instead create a mathematical expression and use it in your query or property definition, for example in the following places:

• At the start of a top-level query, type an equals sign to access the expression builder. You can optionally click the pop-out arrow icon to access a larger workspace for building expressions.

• Filtered to - Type an equals sign to filter events at the start of a query using an arbitrary expression containing ANDs, ORs, or NOTs. These filters are analogous to advanced filters in Scuba version 2. The filter expression can include your event data or your lookup table data.

To access the expression builder, type an equals sign. Tips for using the expression builder are as follows:

• Start typing to see function suggestions.

• Type a left bracket [ to see property suggestions and locally defined measures.

• You can also type numbers that are not suggested.

• After selecting a function, type to see suggested values (for example, after typing MATCHES).

• After selecting a function, you can type a value that is not suggested (for example, in an expression that uses STARTS_WITH).

• After selecting a function, click the function name to view in-product help for the function.

Expression builder syntax

The expression builder uses standard JavaScript syntax for Booleans, as follows:

Matching

The expression builder supports generic matches. For example:

• =MATCHES([action], “like”, “love”)

• =DOES_NOT_MATCH([action], “like”, “love”)

• =IS_EMPTY([action])

• =IS_NOT_EMPTY([action])

Conditional statements

The expression builder includes an IF function. The syntax is as follows:

= IF(condition, value_if_true, value_if_false)

Example uses include:

=IF([action] = "like", [like value], 0) # simple conditional
=IF([num likes] > [num loves], [num likes], [num loves]) # max of two columns

The first parameter can be any filter expression. The second two parameters, the “then” and “else” values, can be anything numeric, including timestamps.

The IF function does not currently support strings or recognize enumeration values like "January", "Monday", flow step names, or termination reasons.

Date math functions

Several relative time functions and calendar-aware functions are available. In addition, several existing functions are calculated based on the time zone set on your Scuba cluster. That is, these functions use a per-cluster time zone setting that is directly viewable and configurable only by contacting customer support or your technical account manager. If your cluster is configured to use UTC, the behavior of these functions won't change. But if it's configured to use some other time zone, these functions honor that time zone.

• The calendar-aware (and time-zone-aware) functions are as follows:

  • DAY_OF_WEEK

  • DAY_OF_MONTH

  • HOUR_OF_DAY

  • MINUTE_OF_HOUR

  • MONTH_OF_YEAR

  • ROUND_TO_YEAR

  • ROUND_TO_MONTH

  • ROUND_TO_DAY

  • ROUND_TO_HOUR

  • ROUND_TO_MINUTE

  • ROUND_TO_SECOND

  • SECOND_OF_MINUTE

• The timestamp difference functions (which do not use the cluster time zone setting) are as follows:

  • SECONDS_BETWEEN

  • MINUTES_BETWEEN

  • HOURS_BETWEEN

  • DAYS_BETWEEN

  • WEEKS_BETWEEN

• The object filter in the expression builder supports string functions, including the following:

  • STARTS_WITH

  • DOES_NOT_START_WITH

  • ENDS_WITH

  • DOES_NOT_END_WITH

  • CONTAINS

  • DOES_NOT_CONTAIN

  • MATCHES_REGEX

Select a function and then click the function name to see in-product help.