Order of Operations

Short version

Filters on dimensions are applied before aggregation. Filters on metrics are applied after. When a metric uses of_all() to exclude a dimension, filters on that excluded dimension are ignored too. That's the gotcha this page exists for.

The pipeline

Every time AQL produces a result, this sequence runs:

1. Create model CTEs.
2. Apply query params (for query models only).
3. Execute AQL dimensions.
4. Ignore excluded dimensions from filters: filters on dimensions that a metric excludes via of_all/exclude are dropped for that metric.
5. Apply filters to dimensions that aren't excluded.
6. Execute aggregations and metric logic.
7. Apply any filters on measures and metrics.

Step 4 is the one that bites most people. The rest of this page is one worked example showing why.

Example: Total Orders per Category for a specific Merchant

Setup: an e-commerce store with orders, order items, products, merchants, and categories.

You want two metrics side by side:

• Total Orders: count(orders.id)
• Total Orders per Category: count(orders.id) | of_all(merchants.name). This ignores the Merchant dimension so the per-category total stays constant across merchants.

// Total Orders
count(orders.id)

// Total Orders Per Category (ignores Merchant)
count(orders.id) | of_all(merchants.name)

The gotcha: filtering Merchant Name still shows empty merchants

Filter the report to merchants.name == 'Abernathy Group'. You'd expect only Abernathy rows. Instead, you also see categories with empty merchant names:

This is step 4 in action. Total Orders per Category excludes merchants.name (via of_all), so the filter on merchants.name is dropped for that metric. The metric computes across all merchants and then gets COALESCE'd onto category rows where Abernathy has no orders.

Generated SQL (abridged)

-- "Abernathy" branch: filter applied
WITH category_orders_abernathy AS (
  SELECT categories.name AS category_name,
         merchants.name AS merchant_name,
         COUNT(orders.id) AS order_count
  FROM ecommerce.order_items
  LEFT JOIN ecommerce.products    ON order_items.product_id = products.id
  LEFT JOIN ecommerce.categories  ON products.category_id   = categories.id
  LEFT JOIN ecommerce.merchants   ON products.merchant_id   = merchants.id
  LEFT JOIN ecommerce.orders      ON order_items.order_id   = orders.id
  WHERE merchants.name = 'Abernathy Group'
  GROUP BY categories.name, merchants.name
),

-- of_all branch: filter dropped because merchants.name is excluded
all_category_orders AS (
  SELECT categories.name AS category_name,
         COUNT(orders.id) AS total_orders
  FROM ecommerce.order_items
  LEFT JOIN ecommerce.products   ON order_items.product_id = products.id
  LEFT JOIN ecommerce.categories ON products.category_id   = categories.id
  LEFT JOIN ecommerce.orders     ON order_items.order_id   = orders.id
  GROUP BY categories.name
)

SELECT COALESCE(a.category_name, b.category_name) AS category_name,
       a.merchant_name,
       MAX(a.order_count)   AS total_orders_abernathy,
       MAX(b.total_orders)  AS total_orders_all_merchants
FROM category_orders_abernathy a
FULL JOIN all_category_orders b USING (category_name)
GROUP BY 1, 2;

Two ways to fix it

Both solutions move the merchant filter out of step 5 (where it's dropped) and into step 7 (where it always runs).

Solution 1. filter via a metric

Create a metric that returns the merchant name, then filter on that metric instead of the dimension.

Because the filter is now on a metric, it lands in step 7, applied to the final result after both branches have computed.

Generated SQL (abridged)

WITH combined_counts AS (
  -- … both branches computed as before, no merchant filter pushed down …
  SELECT category_name,
         merchant_name,
         total_orders,
         total_users_per_category,
         merchant_name AS merchant_name_metric
  FROM /* joined CTEs */
)

SELECT *
FROM combined_counts
WHERE merchant_name_metric = 'Abernathy Group';

Solution 2. bake the condition into the metric

If you don't want a separate filter metric, wrap the calculation in case():

case(
  when: merchants.name == 'Abernathy Group',
  then: count(orders.id) | of_all(merchants.name),
  else: null
)

Now the metric returns null for every merchant except Abernathy. Then filter "is not null" on the metric. Again, that filter runs in step 7.

Generated SQL (abridged)

WITH combined_counts AS (
  SELECT category_name,
         merchant_name,
         MAX(order_count) AS total_orders,
         CASE
           WHEN merchant_name = 'Abernathy Group'
             THEN MAX(total_order_count)
           ELSE NULL
         END AS total_users_per_cate
  FROM /* joined CTEs */
  GROUP BY category_name, merchant_name
)

SELECT *
FROM combined_counts
WHERE total_users_per_cate IS NOT NULL;

Window functions in dimensions evaluate before filters

Window functions in dimensions (rank(), ntile(), previous(), etc.) always run on the full underlying table at step 3, before any filter is applied at step 5. So a rank dimension defined on orders ranks across all orders, not just the ones visible in your report.

dimension revenue_rank {
  model: orders
  definition: @aql rank(order: orders.amount | desc()) ;;
  // Ranks ALL orders. If the report is filtered to 2024,
  // you may see non-consecutive ranks like 145, 203, 567.
}

Two patterns to limit the ranking to a filtered scope:

// Pattern 1: push the filter into the metric and dimensionalize
dimension revenue_rank_2024 {
  model: orders
  definition: @aql
    rank(order: min(orders.amount) | where(orders.created_at >= @2024) | desc())
    | dimensionalize(orders.id, orders.amount)
  ;;
}

// Pattern 2: use case() to control which rows participate
dimension revenue_rank_2024_only {
  model: orders
  definition: @aql
    case(
      when: orders.created_at >= @2024,
      then: rank(order: case(
        when: orders.created_at >= @2024,
        then: orders.amount,
        else: 999999999  // push non-2024 orders to the bottom
      ) | desc()),
      else: null
    )
  ;;
}

For broader LoD patterns (per-customer aggregates, percent of total), see Level of Detail.

FAQ

Are filters on dimensions and metrics applied at the same time, since they live in the same UI panel? No. Dimension filters run before aggregation (step 5); metric filters run after (step 7). Same panel, different stages.

Can I filter data before any dimension is computed? Yes. Use a Query Model with Query Params. Those run at step 2, before anything else.

Why does my rank dimension show non-consecutive numbers after I filter? Because window functions in dimensions evaluate at step 3, before filters at step 5. See Window functions in dimensions above.

How does this interact with PreAggregates? PreAggregates compute a materialized version of metrics ahead of time. They sit conceptually between step 6 and step 7. The aggregated rows are pre-baked, then filters in step 7 still apply against the result.

See also

• Level of Detail: of_all, dimensionalize, exclude
• Metric Context: how filters and grouping flow into metrics
• AQL Validation Rules: hard constraints
• AQL Best Practices: advisory guidance