Skip to main content

where

Definition

Apply condition(s) to modify the measure

Syntax

where(measure, condition, ...)
Examples
where(count(orders.id), orders.status == 'delivered')

// with pipe
count(orders.id) | where(orders.status == 'delivered')

// multiple conditions
count(orders.id) | where(orders.status == 'delivered', orders.created_at matches @(last 7 days))

Input

  • measure: An AQL measure expression.

  • condition (repeatable): A condition that will be applied to the measure. A valid condition must be in following forms:

    • dimension operator value. E.g. orders.status == 'delivered', orders.created_at matches @(last 7 days)
    • dimension in [value1, value2, ...]. E.g. orders.status in ['delivered', 'cancelled']
    • dimension in table. E.g. orders.status in unique(orders.status) | where(orders.status != 'delivered')
    • dimension operator measure. E.g. users.age > avg(users.age)
    info

    If multiple condition are provided, they are evaluated as a logical AND. For example, where(orders.status == refunded', orders.is_cancelled) is equivalent to where(and(orders.status == refunded', orders.is_cancelled)).

Output

New measured with filter applied

Sample Usages

This expression below will evaluate and modify count_orders to only include those with orders.status == 'delivered'

Count orders with status delivered
count(orders.id) | where(orders.status == 'delivered')

where() can also be applied to a pre-defined measure

orders.count_orders | where(orders.status == 'delivered')

Using where() to create a semi-additive measure. For example, if you want to create a measure that only count orders that are created in the last day, you can use where() to apply the condition to the count(orders.id) measure:

Count orders created in the last day
explore {
dimensions {
orders.created_at | year()
}

measures {
orders.count_orders,
orders_on_last_day_of_month:
orders.count_orders | where(orders.created_date == max(orders.created_date))
}
}

FAQ

What is the difference between where() vs filter()

To understand when should you use where() or filter() to apply filtering condition to a transformation, please visit this document: where vs filter


Let us know what you think about this document :)