> ## 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.

# dynamic_top_n

> Generates dimensions that automatically filter to the top N or bottom N values based on a measure.

This parameter is useful for creating pre-defined rankings like "Top 10 States by Sales" or "Bottom 5 Products by Revenue" without requiring manual filters. This allows you to:

* Create dimensions that show only the most relevant values for analysis
* Improve query performance by limiting the number of returned values, or
* Build reports with ranking without maintaining manual lists

When used, Omni generates a query that:

1. Aggregates the specified measure
2. Sorts by that measure
3. Limits to the specified number of values
4. Returns the dimension values that meet the criteria

## Syntax

```yaml theme={null}
<dimension_name>:
  sql: <sql_expression>
  dynamic_top_n:
    n: <number>
    by: <measure_reference>
    desc: <true|false>
    else: <string|null>
```

## 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="sql" type="string" required>
      The SQL expression that defines the dimension field.
    </ParamField>

    <ParamField path="dynamic_top_n" type="object">
      Configuration for dynamic top N or bottom N filtering.

      <Expandable title="dynamic_top_n properties" defaultOpen="true">
        <ParamField path="n" type="number" required>
          The number of values to return (e.g., `10` for top 10).
        </ParamField>

        <ParamField path="by" type="string" required>
          A reference to the measure used for sorting and ranking. Use the format `view_name.measure_name`.
        </ParamField>

        <ParamField path="desc" type="boolean" default="true">
          Sort order for the ranking:

          * `true` (default): Returns the top N values (highest to lowest)
          * `false`: Returns the bottom N values (lowest to highest)
        </ParamField>

        <ParamField path="else" type="string | null" default="'Other'">
          Controls how values outside the top N or bottom N are handled:

          * String value (e.g., `"Other"`, `"Remaining"`) - Groups all non-top-N values under the specified label
          * `null` - Excludes non-top-N values entirely, showing only the top N values without an aggregated group

          By default, non-top-N values are grouped as `Other`.
        </ParamField>
      </Expandable>
    </ParamField>
  </Expandable>
</ParamField>

## Query-time parameterization

You can override the default `n` value at query time without modifying the model definition. This allows you to dynamically adjust the number of returned values for analysis.

Use the syntax `field_name[number]` where `number` overrides the model's default `n` value. For example, if you have a dimension named `top_5_states` with `n: 5` in the model, you can use `top_5_states[2]` in your query to limit results to the top 2 states instead.

When you override the `n` value:

* The dimension returns the specified number of top or bottom values based on the query-time parameter
* The `Other` bucket (if configured with the [`else` parameter](#param-else) automatically adjusts to include all remaining values
* If no parameterization is provided, the dimension uses the model-defined default `n` value

Consider the following example:

```yaml title="Model definition" theme={null}
dimensions:
  top_5_states:
    sql: ${users.state}
    dynamic_top_n:
      n: 5
      by: orders.count
```

And in the query, to override the defined `n` value:

* `top_5_states` returns 6 rows: the top 5 states + `Other`
* `top_5_states[2]` returns 3 rows: the top 2 states + `Other`
* `top_5_states[10]` returns 11 rows: the top 10 states + `Other`

## Examples

```yaml title="Top 10 states by order count" theme={null}
dimensions:
  top_10_states_by_orders:
    sql: ${users.state}
    dynamic_top_n:
      n: 10
      by: orders.count
```

```yaml title="Bottom 3 countries by order count" theme={null}
dimensions:
  bottom_3_countries_by_orders:
    sql: ${users.country}
    dynamic_top_n:
      n: 3
      by: orders.count
      desc: false
```

```yaml title="Top 20 products by revenue" theme={null}
dimensions:
  top_products_by_revenue:
    sql: ${products.name}
    dynamic_top_n:
      n: 20
      by: order_items.total_revenue
```

```yaml title="Top 5 sales reps by deal value" theme={null}
dimensions:
  top_sales_reps:
    sql: ${sales_reps.name}
    label: Top 5 Sales Reps
    dynamic_top_n:
      n: 5
      by: deals.total_value
      desc: true
```

```yaml title="Top 10 customers with custom label for remaining values" theme={null}
dimensions:
  top_10_customers:
    sql: ${customers.name}
    dynamic_top_n:
      n: 10
      by: orders.total_sales
      else: "All Other Customers"
```

```yaml title="Top 5 categories without grouping remaining values" theme={null}
dimensions:
  top_5_categories_only:
    sql: ${products.category}
    dynamic_top_n:
      n: 5
      by: order_items.total_revenue
      else: null
```