> ## Documentation Index
> Fetch the complete documentation index at: https://docs.omni.co/llms.txt
> Use this file to discover all available pages before exploring further.

# level_of_detail

> Creates a Level of Detail field, which controls the granularity at which an aggregation is computed.

Level of Detail (LoD) fields control the granularity at which an aggregation is computed. When used as a dimension, an LoD produces a categorical value — one row per level of detail — which lets you perform an additional layer of aggregation (e.g., average customer lifetime spend).

<Tip>
  You can also create LoD fields in the workbook! In the field browser of a query tab, click the <Icon icon="ellipsis-vertical" type="solid" /> on a dimension and then **Modeling > New level of detail field**.
</Tip>

## How it works

The easiest way to understand an LoD dimension is to read it as SQL. For example, this `max_age_in_country` LoD finds the maximum user age within each country and attaches it to every user row:

<CodeGroup>
  ```yaml Model theme={null}
  max_age_in_country:
    sql: ${users.age}
    level_of_detail:
      aggregate_type: max
      fixed: [country]
  ```

  ```sql SQL equivalent theme={null}
  SELECT
    users.country AS "country",
    users.id AS "id",
    lod.max_age AS "max_age_in_country"
  FROM users
  INNER JOIN (
      SELECT country,
             MAX(age) AS max_age
        FROM users
    GROUP BY country
  ) lod ON users.country = lod.country
  ```
</CodeGroup>

The `fixed: [country]` clause corresponds to the `GROUP BY country` in the inner query — one row per country, the level of detail. The join then carries that country-level value back to each user row, which is why the LoD behaves like a categorical dimension: every user in the same country shares the same `max_age_in_country` value.

## Syntax

```yaml theme={null}
<dimension_name>:
  level_of_detail:
    aggregate_type: <aggregation>
    <grouping_strategy>: [<field_name>]
    custom_primary_key_sql: <field_reference>
    cancel_query_filters: <true_false>
    filters:
      <field>:
        <filter_operator>: <value>
        cancel_query_filter: <true_false>
```

## Properties

<ParamField path="dimension_name" type="object">
  The name of the dimension. Dimension names must:

  * Be unique within the view
  * Start with a letter
  * Contain only alphanumeric characters and underscores

  <Expandable title="dimension_name properties" defaultOpen="true">
    <ParamField path="level_of_detail" type="object">
      Configures the level of detail calculation for this dimension.

      <Expandable title="level_of_detail properties" defaultOpen="true">
        <ParamField path="aggregate_type" type="string" required>
          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:

          * `sum_distinct_on`
          * `average_distinct_on`
          * `median_distinct_on`
          * `percentile_distinct_on`
        </ParamField>

        <ParamField path="grouping_strategy" type="string" required>
          The grouping strategy and the field to use to apply it, specified as `grouping_strategy: [ field_name ]`. Refer to the [Examples](#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
        </ParamField>

        <ParamField path="custom_primary_key_sql" type="string">
          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_type`s are used:

          * `sum_distinct_on`
          * `average_distinct_on`
          * `median_distinct_on`
          * `percentile_distinct_on`
        </ParamField>

        <ParamField path="cancel_query_filters" type="boolean" default="false">
          When `true`, ignores any row filters applied in the outer query when computing the LoD metric. Defaults to `false`.

          To selectively ignore specific filters while keeping others, use the `filters` parameter with [`cancel_query_filter`](/modeling/filters/operators/cancel-query-filter) instead.
        </ParamField>

        <ParamField path="filters" type="object">
          Applies filters to the level of detail calculation. See the [Filters documentation](/modeling/filters) for more information on Omni filter syntax.

          You can use [`cancel_query_filter: true`](/modeling/filters/operators/cancel-query-filter) 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`](#param-cancel-query-filters), which ignores all global filters.
        </ParamField>
      </Expandable>
    </ParamField>
  </Expandable>
</ParamField>

## Examples

```yaml title="Customer lifetime spend" theme={null}
customer_lifetime_spend:
  sql: ${sale_price}
  label: Customer Lifetime Spend
  level_of_detail:
    aggregate_type: sum
    always_include: [user_id]
    cancel_query_filters: true
```

```yaml title="Fixed level of detail" theme={null}
category_total:
  sql: ${revenue}
  level_of_detail:
    aggregate_type: sum
    fixed: [category]
```

```yaml title="Exclude dimensions from grouping" theme={null}
overall_average:
  sql: ${score}
  level_of_detail:
    aggregate_type: average
    always_exclude: [region]
```

```yaml title="Sum distinct on with custom primary key" theme={null}
sum_by_order:
  sql: ${order_items.amount}
  level_of_detail:
    aggregate_type: sum_distinct_on
    custom_primary_key_sql: ${order_items.order_id}
    fixed: []
```

```yaml title="Selectively ignore a specific global filter" theme={null}
arr_balance_by_logo:
  sql: ${test_query_view.arr_cumulative}
  level_of_detail:
    aggregate_type: max
    fixed: [logo, date]
    filters:
      date:
        is: ""
        cancel_query_filter: true  # Ignores global date filter for this level of detail only
```

```yaml title="Replace a cancelled filter with a different value" theme={null}
compare_to_california:
  sql: ${revenue}
  level_of_detail:
    aggregate_type: sum
    fixed: [state]
    filters:
      state:
        is: California  # Replaces any global state filter with California
        cancel_query_filter: true
```

```yaml title="Compare with cancel_query_filters (all-or-nothing)" theme={null}
customer_lifetime_spend:
  sql: ${sale_price}
  level_of_detail:
    aggregate_type: sum
    always_include: [user_id]
    cancel_query_filters: true  # Ignores all global filters
```