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

Result

SQL

SELECT
  COUNT("orders"."id") AS "count_orders->id"
FROM
  "demo"."orders" "orders"
WHERE
  "orders"."status" = 'delivered'

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

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

Open in AMQL playground

Result

SQL

SELECT
  COUNT("orders"."id") AS "orders->count_orders"
FROM
  "demo"."orders" "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))
  }
}

Open in AMQL playground

Result

SQL

WITH "aql__t1" AS (
  SELECT
    (DATE_TRUNC ( 'year', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "orders->created_at->year",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "demo"."orders" "orders"
  GROUP BY
    1
), "aql__t4" AS (
  SELECT
    "orders"."created_at" AS "created_at",
    (DATE_TRUNC ( 'year', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS "created_at->year",
    "orders"."id" AS "id",
    CAST ( (CAST ( (DATE_TRUNC ( 'day', (CAST ( "orders"."created_at" AS timestamptz )) AT TIME ZONE 'Asia/Saigon' )) AT TIME ZONE 'Asia/Saigon' AS timestamptz )) AT TIME ZONE 'Asia/Saigon' AS date ) AS "created_date"
  FROM
    "demo"."orders" "orders"
), "aql__t3" AS (
  SELECT
    "orders"."created_at->year" AS "orders->created_at->year",
    MAX("orders"."created_date") AS "max_orders->created_date"
  FROM
    "aql__t4" "orders"
  GROUP BY
    1
), "aql__t5" AS (
  SELECT
    "orders"."created_date" AS "orders->created_date"
  FROM
    "aql__t4" "orders"
    LEFT JOIN "aql__t3" ON "orders"."created_at->year" = "aql__t3"."orders->created_at->year" OR ("orders"."created_at->year" IS NULL AND "aql__t3"."orders->created_at->year" IS NULL)
  WHERE
    CAST ( "orders"."created_date" AS date ) = CAST ( "aql__t3"."max_orders->created_date" AS date )
  GROUP BY
    1
), "aql__t6" AS (
  SELECT
    "orders"."created_at->year" AS "orders->created_at->year",
    COUNT("orders"."id") AS "count_orders->id"
  FROM
    "aql__t4" "orders"
    LEFT JOIN "aql__t5" ON "orders"."created_date" = "aql__t5"."orders->created_date"
  WHERE
    "aql__t5"."orders->created_date" IS NOT NULL
  GROUP BY
    1
)
SELECT
  TO_CHAR((CAST ( COALESCE("aql__t1"."orders->created_at->year", "aql__t6"."orders->created_at->year") AS timestamptz )) AT TIME ZONE 'Asia/Saigon', 'YYYY-MM-DD HH24:MI:SS.US') AS "orders->created_at->year",
  MAX("aql__t1"."count_orders->id") AS "orders->count_orders",
  MAX("aql__t6"."count_orders->id") AS "orders_on_last_day_of_month"
FROM
  "aql__t1"
  FULL JOIN "aql__t6" ON "aql__t1"."orders->created_at->year" = "aql__t6"."orders->created_at->year"
GROUP BY
  1
HAVING
  (MAX("aql__t1"."count_orders->id") IS NOT NULL) OR
  (MAX("aql__t6"."count_orders->id") IS NOT NULL)

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);;
}