Improving date flexibility with templated filters

Omni provides powerful, built-in options for manipulating dates on a dashboard, such as time frame switchers and period over period analysis. However, if you want to give your users even more control, templated filters are the answer. This guide walks through two advanced patterns for date flexibility:

Granularity control with automated filtering: A single control that changes the granularity of time series charts (e.g., month to quarter) while simultaneously filtering to the “last complete” period.
The “everything” toggles: Giving you the ability to swap the underlying date field, timeframe granularity, and date range all from a single dashboard interface using multiple filters.

Implementing these patterns allows you to create a system where you can redefine the context of your data on the fly.

Requirements

To follow the steps in this guide, you’ll need:

Modeler or Connection Admin permissions for the model you want to work with
A basic understanding of templated filters

Pattern 1: Dynamic charts with “last complete period” logic

When looking at data over time, comparing a partial current month against a full previous month can be misleading. This pattern creates a dimension that automatically nulls out the current, incomplete period based on the granularity you’ve selected.

Create a filter-only field for granularity

In your model, define a string filter that will act as the toggle:

  date_granularity:
    type: string
    label: Date Granularity
    description: Select the timeframe for the completion filter
    suggestion_list:
      - value: Month
      - value: Quarter
      - value: Year
    filter_single_select_only: true

Build the dynamic date dimension

Next, create a custom dimension using templated filters. This dimension will evaluate the user’s selection and apply the “last complete” logic.For example, when Quarter is selected, the CASE statement returns NULL for the current (incomplete) quarter and truncates to the quarter otherwise. The same pattern applies for Year and Month.Use the following code to add the dimension, replacing saas__opportunities.created_date with your own view and date field:

  created_date_completed_periods:
    sql: |-
      CASE
        WHEN {{# saas__opportunities.date_granularity.filter }} 'Quarter' {{/ saas__opportunities.date_granularity.filter }}
          THEN CASE
            WHEN DATE_TRUNC('quarter', ${saas__opportunities.created_date}) >= DATE_TRUNC('quarter', CURRENT_DATE)
              THEN NULL
            ELSE DATE_TRUNC('quarter', ${saas__opportunities.created_date})
          END
        WHEN {{# saas__opportunities.date_granularity.filter }} 'Year' {{/ saas__opportunities.date_granularity.filter }}
          THEN CASE
            WHEN DATE_TRUNC('year', ${saas__opportunities.created_date}) >= DATE_TRUNC('year', CURRENT_DATE)
              THEN NULL
            ELSE DATE_TRUNC('year', ${saas__opportunities.created_date})
          END
        ELSE CASE
          WHEN DATE_TRUNC('month', ${saas__opportunities.created_date}) >= DATE_TRUNC('month', CURRENT_DATE)
            THEN NULL
          ELSE DATE_TRUNC('month', ${saas__opportunities.created_date})
        END
      END
    label: Created Date (Completed Periods)

Implement on a dashboard

In a workbook, select the Created Date (Completed Periods) dimension for your time series charts.
Navigate to your dashboard and click Add > Filter.
Configure the filter to point at your date_granularity field.

Now when the filter is used, the connected tiles will automatically update to use the selected granularity:

Date Granularity — The granularity switcher in action, automatically hiding the current incomplete period.

Pattern 2: The “everything” toggles

This pattern gives you three independent dashboard controls: the date field (e.g., switching from Created Date to Shipped Date), the granularity, and the date range window.

Define the selection filters

In your model, create two filter-only fields: one for the field choice and one for the granularity.

  as_of_date_field:
    type: string
    label: As of Date
    suggestion_list:
      - value: Created At
      - value: Shipped At
      - value: Delivered At
      - value: Returned At
    default_filter:
      is: Created At
    filter_single_select_only: true

  as_of_date_field_granularity:
    type: string
    label: Date Granularity
    suggestion_list:
      - value: Daily
      - value: Weekly
      - value: Monthly
    default_filter:
      is: Daily
    filter_single_select_only: true

Create the dynamic logic

This pattern requires two dimensions to work: one to swap the raw date field, and the second to truncate that field based on the selected granularity.

Dimension 1: Swap raw date field
Dimension 2: Truncate field

The dynamic_date dimension uses a CASE statement to swap the underlying source column (for example, created_at vs. shipped_at) based on the filter selection:

  dynamic_date:
    sql: |-
      CASE
        WHEN {{# order_items.as_of_date_field.filter }} 'Created At' {{/ order_items.as_of_date_field.filter }} THEN ${order_items.created_at}
        WHEN {{# order_items.as_of_date_field.filter }} 'Shipped At' {{/ order_items.as_of_date_field.filter }} THEN ${order_items.shipped_at}
        WHEN {{# order_items.as_of_date_field.filter }} 'Delivered At' {{/ order_items.as_of_date_field.filter }} THEN ${order_items.delivered_at}
        WHEN {{# order_items.as_of_date_field.filter }} 'Returned At' {{/ order_items.as_of_date_field.filter }} THEN ${order_items.returned_at}
      ELSE ${order_items.created_at}
      END
    label: Dynamic Date

The dynamic_date_granularity dimension takes the result of dynamic_date and applies the truncation level (daily, weekly, or monthly) that you’ve selected:

  dynamic_date_granularity:
    sql: |-
      CASE
        WHEN {{# order_items.as_of_date_field_granularity.filter }} 'Daily' {{/ order_items.as_of_date_field_granularity.filter }} THEN ${order_items.dynamic_date[date]}
        WHEN {{# order_items.as_of_date_field_granularity.filter }} 'Weekly' {{/ order_items.as_of_date_field_granularity.filter }} THEN ${order_items.dynamic_date[week]}
        WHEN {{# order_items.as_of_date_field_granularity.filter }} 'Monthly' {{/ order_items.as_of_date_field_granularity.filter }} THEN ${order_items.dynamic_date[month]}
      ELSE ${order_items.dynamic_date[date]}
      END
    label: Dynamic Date Granularity

Build the view

In a workbook, build your charts using the Dynamic Date Granularity dimension.
When finished, complete the following on the document’s dashboard:
1. Add filters to the dashboard for both As of Date and Date Granularity.
2. Add a standard date range filter and map it to the Dynamic Date Granularity field to control the window of time.

Everything Toggle — Swapping date fields, granularity, and date ranges all from the dashboard UI.

Next steps

Learn more about filter-only fields to create more interactive toggles.
Learn how to map dashboard filters to different tile fields to ensure your filters apply correctly across tiles.