Dynamically Change Date Granularity Based on Filter Timeframe

Patterns
1

When working with time series data, you may be commonly switching between hourly/daily/weekly or even other time granularities. Omni provides a built-in way to flip between these options using the Time Frame Switcher control.

However, maybe you want this timeframe to switch automatically based on the date filter selection, such as showing you hourly numbers when filtered to < 2 days of data and flipping to daily when > 2 days. Here’s a working pattern to do this in Omni (see video at the end for a walkthrough!):

  1. Figure out which date field will drive all of this, and add it as a filter to your query/dashboard
  2. Open the Workbook IDE (Model → Model layers → Workbook) and navigate to the view file (shortcut: click the three dots next to view name → Modeling → Go to definition)
  3. Paste the code below as a new dimension and modify the field/view references. Also modify the threshold and/or add criteria as needed
  4. Back in the workbook, build your query using this new Dynamic Date Part and any other fields you need

That’s it! Now you should observe the dynamic date aggregating differently when above/below the threshold(s) you specified!

Snowflake

dynamic_date_part:
    sql: |
      case
        when timestampdiff(hour, {{filters.omni_dbt_ecomm__order_items.created_at.range_start}}, {{filters.omni_dbt_ecomm__order_items.created_at.range_end}}) <= 24*2
          then TO_CHAR(${omni_dbt_ecomm__order_items.created_at[hour]}, 'MM-DD-YYYY HH24:MI')
        else TO_CHAR(${omni_dbt_ecomm__order_items.created_at[date]}, 'MM-DD-YYYY')
      end
    label: Dynamic Time Frame
    description: Switches between daily and hourly based on the dates selected

BigQuery

dynamic_date_part:
    sql: |
      case
        when timestamp_diff({{filters.omni_dbt_ecomm__order_items.created_at.range_start}}, {{filters.omni_dbt_ecomm__order_items.created_at.range_end}}, hour) <= 24*2
          then cast(${omni_dbt_ecomm__order_items.created_at[hour]} as string)
        else FORMAT_TIMESTAMP('%B %d, %Y',${omni_dbt_ecomm__order_items.created_at[date]})
      end
    label: Dynamic Time Frame
    description: Switches between daily and hourly based on the dates selected

Other Dialects

This should work with all SQL dialects, just replace the timestampdiff and TO_CHAR syntax with the syntax required by your dialect.

Video Walkthrough

1 Like
2

This is a super useful pattern for keeping the chart legible as you traverse larger ranges of data.

Worth noting that you can combine this with PoP (best when limited to “prior period”) if you just divide the datediff() by 2 and wrap in ROUND() function.

Also if you want to allow end users to override the automatic grain selection (like a timeframe swapper) you can use a field switcher instead, setting the dynamic timeframe field as the default option and the grabbing the timeframes of the actual date field as alternative options.

This lets the user see the trend in the automated grain but then decided they want to see it more granular and override the automation.

1 Like