Level of detail
Sometimes a metric needs to be calculated at a different grain than the report it appears in. Level-of-detail functions let you override that grain.
By default, a metric is calculated at whatever grain the surrounding report is using. Group by country → it aggregates per country. Group by month → it aggregates per month.
Level of detail (LoD) is when you want a metric to aggregate at a different grain than the surrounding report uses. Three terms worth pinning down:
- Data LoD: the grain of the rows you have.
order_itemsis high (one row per item);monthly_salesis low. - Visualization LoD: the dimensions in the report.
- Calculation LoD: the dimensions the metric actually uses. Usually equal to Visualization LoD; LoD functions are how you make them differ.
Three classic cases where Calc LoD ≠ Viz LoD:
- Percent of total: denominator is "across everything", regardless of the current grouping (Calc LoD lower than Viz LoD).
- Max of per-customer metric by country: AOV per customer first, then max within country (Calc LoD higher than Viz LoD).
- Fixed-grain dimension: a customer's lifetime revenue, used as a filter or category (Calc LoD fixed, independent of Viz LoD).
AQL has functions for each. They're all "context modifiers" in the sense of metric context.
Try it interactively
Practice in the AQL Playground: Level of Detail · Function: of_all · Function: dimensionalize.
of_all(): drop dimensions out of the calculation
of_all(<model>) tells a metric to ignore any dimension from that model. Percent of total:
metric country_share {
definition: @aql
count(orders.id) * 1.0
/ (count(orders.id) | of_all(countries))
;;
}The numerator follows the report's grouping (per country). The denominator ignores countries and stays the global total.
exclude(): same idea, different angle
Where of_all(countries) says "ignore only countries", exclude(<model>) says "ignore everything except this model's grain". Useful when the report has many dimensions and you want one to win.
dimensionalize(): make an aggregation usable as a dimension
dimensionalize(<grain>) evaluates an aggregation at a fixed grain, then exposes the result as a dimension. This is how you build things like "VIP customer" flags:
dimension is_vip_customer {
model: users
type: 'truefalse'
definition: @aql
(sum_revenue > 10000 and count(orders.id) >= 5)
| dimensionalize(users.id)
;;
}Now users.is_vip_customer works like any other dimension (filter on it, group by it).
Choosing between them
Pick by how Calculation LoD relates to Visualization LoD:
| Calc LoD vs Viz LoD | Reach for |
|---|---|
| Lower (denominator across everything) | of_all() |
| Higher (aggregate of an aggregate) | Nested aggregation with group() + select() |
| Fixed (a reusable dimension at a set grain) | dimensionalize() |
| Single grain regardless of report | exclude() |
| Switches by which dimension is active in a pivot | is_at_level(): see Cookbook use case 4 |
For worked end-to-end examples, see Level of Detail Patterns in the cookbook.
Next
→ Nested aggregation: when you need to aggregate the results of an aggregation.
Metric context
A metric doesn't carry a fixed value. It gets evaluated against the dimensions and filters around it. That surrounding state is the metric's context.
Nested aggregation
Sometimes you need to aggregate the results of an aggregation. AQL handles this with the group() + select() pattern inside another aggregator.