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.

relative_period(): shift the metric back in timeCopy Anchor Link

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 thereCopy Anchor Link

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 modifiersCopy Anchor Link

Why this is one definition, not manyCopy Anchor Link

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 arcCopy Anchor Link

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.