Use this file to discover all available pages before exploring further.
Level of Detail (LoD) fields control the granularity at which an aggregation is computed. When used as a measure, an LoD produces an aggregation of an aggregation, such as an average of averages or a percent of total.
The easiest way to understand an LoD measure is to read it as SQL. For example, this avg_customer_lifetime_spend LoD first sums each customer’s order amounts, then averages those per-customer totals at whatever grain the query is run. In this example, that’s by country:
avg_customer_lifetime_spend: sql: ${orders.amount} aggregate_type: average level_of_detail: aggregate_type: sum fixed: [customers.id]
The level_of_detail block defines the inner query: fixed: [customers.id] with aggregate_type: sum produces one summed row per customer — that’s the first aggregation. The measure’s aggregate_type: average then takes those per-customer totals and averages them at the query’s grain. That’s the “aggregation of an aggregation” shape: a per-customer SUM feeding into a per-country AVG.Note that the outer GROUP BY customers.country clause comes from the dimensions selected in the query, not the LoD definition itself.
The type of aggregation to apply. Supports standard aggregations like sum, average, count, min, max, and distinct-on aggregations.When the following aggregations are used, the custom_primary_key_sql parameter is required:
The grouping strategy and the field to use to apply it, specified as grouping_strategy: [ field_name ]. Refer to the Examples section to see an example with complete syntax.The grouping strategy must be one of the following:
always_include - Adds specified dimensions to the query’s grouping, forcing a finer level of detail
always_exclude - Removes specified dimensions from the grouping, producing a coarser aggregation
fixed - Defines an absolute level of detail, replacing all query groupings
A field reference that defines the key to use for deduplication when using *_distinct_on aggregate types, specified using ${} syntax. This allows you to aggregate over a different level than the view’s primary key, such as summing amounts by order ID when your view is at the order items level.This parameter is required when the following aggregate_types are used:
When true, ignores any row filters applied in the outer query when computing the level of detail metric. Defaults to false.To selectively ignore specific filters while keeping others, use the filters parameter with cancel_query_filter instead.
Applies filters to the level of detail calculation. See the Filters documentation for more information on Omni filter syntax.You can use cancel_query_filter: true within a filter to selectively ignore specific global filters for this level of detail only, while keeping other global filters active. This provides granular control compared to cancel_query_filters, which ignores all global filters.
measures: arr_balance: aggregate_type: sum sql: ${accounts.arr_cumulative} level_of_detail: aggregate_type: max fixed: [accounts.logo, orders.date] filters: orders.date: cancel_query_filter: true # Ignores global date filter for this level of detail only
Replace a cancelled filter with a different value
measures: california_revenue: aggregate_type: sum sql: ${order_items.revenue} level_of_detail: aggregate_type: sum fixed: [stores.state] filters: stores.state: is: California # Replaces any global state filter with California cancel_query_filter: true
Compare with cancel_query_filters (all-or-nothing)
measures: total_lifetime_value: aggregate_type: sum sql: ${order_items.sale_price} level_of_detail: aggregate_type: sum always_include: [users.user_id] cancel_query_filters: true # Ignores all global filters
