Time comparisons
"Revenue this year vs last year" is one of the most common things a dashboard needs. In SQL it's an annoying self-join. In AQL it's a context modifier: take your existing metric, shift its time context, compare.
Practice in the AQL Playground: Function: date_trunc · Date/Time Expressions.
relative_period(): shift the metric back in time
relative_period(<time dim>, interval(-1 year)) says "evaluate this metric one year earlier than the surrounding report".
metric revenue_previous_year {
definition: @aql
sum_revenue
| relative_period(orders.created_at, interval(-1 year))
;;
}
If the report's looking at March 2024, this evaluates to March 2023. If it's looking at Q3 2024, this becomes Q3 2023. The metric automatically adapts to whatever time granularity the report uses.
Building period-over-period from there
Once you have the shifted metric, the comparison metrics are just arithmetic:
metric revenue_yoy_change {
definition: @aql sum_revenue - revenue_previous_year ;;
}
metric revenue_yoy_pct {
definition: @aql
safe_divide(
(sum_revenue - revenue_previous_year) * 100.0,
revenue_previous_year
)
;;
}
Drop revenue_yoy_pct into a monthly trend chart and every month shows its YoY change. Drop it into a quarterly view and every quarter does. Same metric definition.
Other time modifiers
| Want… | Function |
|---|---|
| Shift back by an interval | relative_period |
| Compare to the same date in a previous year/quarter | exact_period |
| Year-to-date / month-to-date | period_to_date |
| Rolling window (last N days) | trailing_period |
| Running total | running_total |
| Compare to the previous row's value | previous |
Why this is one definition, not many
The pattern is always the same: take an existing metric, pipe it through a time modifier, get a comparable metric. You never duplicate the underlying aggregation, you never write a self-join, and you never tie the metric to a specific year.
This is the payoff of metric context: once you understand context as a thing you can override, period comparisons are just one specific kind of override.
What's next after the arc
You've now seen every major piece of AQL. From here:
- Examples & Recipes: short, copy-paste patterns on a shared e-commerce schema.
- Reference: function signatures.
- AQL Best Practices: what to do and not do.