Holistics AQL learning Lesson 0006

AQLearn 1.5 / Concept

Filters

Source lesson: AQLearn 1.5: Filters. Tangible win: narrow your measure to a specific segment of data using explore filters {}.

Purpose of this lesson

Have you ever tried to answer "How many users from a target segment signed up in the last 2 years, by year"?

You hit these pain points:

  1. Without a filter, the measure counts everyone for all time, so the number you ship is wrong and you do not notice until a customer pushes back.
  2. Hardcoded dates like '2024-01-01' rot. The dashboard silently drifts as time passes, and you have to remember to edit the query each quarter.
  3. Stacking conditions (date AND last name in a list) by hand in SQL is fragile; one missing parenthesis or one OR in the wrong place changes the answer.

This lesson solves it: write explore filters {} with the right operator and datetime literal so the segment is narrowed correctly, stays correct over time, and reads like the business question.

Why this matters for your mission: almost every customer question contains a filter, "in 2024", "for VIPs", "from Vietnam". Misplaced or wrong filters are the most common source of "wrong numbers" complaints.

Core idea

An explore filter is a boolean expression that picks which rows feed the measures. AQL evaluates the filter for every row before aggregating.

explore {
  measures {
    count_of_users: users | count(users.id),
  }
  filters {
    users.sign_up_at > @2024,
  }
  dimensions {
    time: users.sign_up_at | year(),
  }
}

Read: "Count users whose sign_up_at is after January 1, 2024, broken down by sign-up year."

Datetime literal @

AQL uses @ to express datetime literals naturally.

LiteralMeans
@2024January 1, 2024 00:00:00
@2024-03March 1, 2024 00:00:00
@2024-03-12March 12, 2024 00:00:00
@(3 years ago)Relative datetime, computed at query time
@(1 month ago)Same idea, rolling window

Common filter operators

Comparison

>, <, >=, <=, ==, !=.

orders.created_at > @2024

List membership

in for "one of these values".

users.last_name in ['Kasey', 'Stacey']

String match

like with SQL wildcards %.

users.email like '%.com'

Range

between for inclusive bounds.

orders.discount between 0.1 and 0.3

Multiple filters separated by commas are combined with logical AND.

Dataset diagram

This lesson queries users as the source model. The filter users.sign_up_at > @2024 narrows the row set before any aggregate runs.

N : 1 N : 1 N : 1 N : 1 N : 1 countries codetext, pk nametext continent_nametext cities idnumber, pk nametext country_codetext, fk users this lesson idnumber, pk first_nametext last_nametext emailtext gendertext sign_up_atdatetime city_idnumber, fk orders idnumber, pk statustext created_atdatetime delivery_attemptsnumber discountnumber user_idnumber, fk order_items order_idnumber, fk product_idnumber, fk quantitynumber products idnumber, pk nametext pricenumber category_idnumber, fk merchant_idnumber, fk
Highlighted field: users.sign_up_at. Filters in this lesson narrow user rows by this datetime, by last name, and by their combination.
primary key foreign key

Worked examples

Users who signed up before 2022

explore {
  measures {
    count_of_users: users | count(users.id),
  }
  filters {
    users.sign_up_at < @2022,
  }
  dimensions {
    time: users.sign_up_at | year(),
  }
}

Same thing, relative datetime

If "today" is during 2024, "before 2022" is the same as "more than 2 years ago".

filters {
  users.sign_up_at < @(2 years ago),
}

Relative literals are useful for rolling dashboards that should stay correct as time passes.

Users named Kasey or Stacey

filters {
  users.sign_up_at < @2022,
  users.last_name in ['Kasey', 'Stacey'],
}

Two filters, AND-combined. Both must be true for a row to count.

Break down further

Add users.last_name as a second dimension to see the count per (year, last name) cell.

dimensions {
  time: users.sign_up_at | year(),
  last_name: users.last_name,
}
Customer-safe explanation: "Filters decide which rows are even considered. Dimensions decide how the remaining rows are sliced. Measures compute the number per slice."

Retrieval practice

1. Two filter lines separated by a comma combine using which logic?
2. Which operator best fits "last name is Kasey or Stacey"?
3. @(3 years ago) is a...
4. When does an explore filter run?

Do in AQLearn

  1. Open AQLearn Filters.
  2. Change the filter to users.sign_up_at < @2022 and run.
  3. Replace it with users.sign_up_at < @(2 years ago) and confirm the same row count.
  4. Add filter users.last_name in ['Kasey', 'Stacey'].
  5. Add dimension last_name: users.last_name and run. Read result as count per year and last name.

Next local lesson

Next: relationships. How AQL traverses model-to-model paths without explicit SQL joins.