Skip to main content

where

Definition​

Apply condition(s) to modify a measure/metric.

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')
Open in AMQL playground

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

orders.count_orders | where(orders.status == 'delivered')
Open in AMQL playground

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))
  }
}
Open in AMQL playground

When not to use where()?​

Sometimes you want to filter an arbitrary expression that haven't been explicitly defined as a dimension yet. For example, you want to know which country has at least 100,000 users who have placed at least 1 order:

orders
| group(orders.country)
| select(orders.country, users_count: count_distinct(orders.user_id))
| where(users_count >= 100000) // `where` here will be invalid

In this case, where() cannot be used, because by definition, where() have to filter on an existing dimension of orders, while the field users_count is an aggregation that has not been defined and calculated before. You can use filter() function instead.

orders
| group(orders.country)
| select(orders.country, users_count: count_distinct(orders.user_id))
| filter(users_count >= 100000) // `filter` works here because it acts on table

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

I can't apply where() to a dimensionalized measure?​

where() cannot be applied to a dimension. If you want to dimensionalize a measure (i.e turning a measure into a dimension), and you want to apply where() to the resulting dimension, don't do this:

// DON't do this
dimension dimesionalized_measure {
    // ... 
    definition: @aql min(orders.created_at | month()) | dimensionalize(users.id);;
}

dimension another_dimension {
  // ...
    definition: @aql dimesionalized_measure | where(orders.country == 'Vietnam');; 
}

If you do this, you won't get the desired result.

Instead, do this:

dimension dimesionalized_measure {
    // ... 
    definition: @aql min(orders.created_at | month()) | where(orders.country == 'Vietnam') | dimensionalize(users.id);;
}
Let us know what you think about this document :)