Timelines with dynamic date granularity

[zlebowitz]

Let’s say I have graph on a dashboard that shows sales over time:

In this example, I’m looking at 90 days of daily data. Daily seems like a reasonable granularity for a 90 day time frame. But what if I filter down the dashboard to 3 days? Wouldn’t it be great if my dashboard could automatically adjust the x-axis to display finer-grained time slices? Hourly maybe?

Turns out you can set that up with just a couple of lines of LookML!

All I need is one new dimension, which returns different time granularities based on the users’s filter selection.

  dimension: time {
    sql:
    CASE 
      WHEN 
        datediff(
                'day', 
                cast({% date_start created_date %} as date), 
                cast({% date_end created_date  %} as date)
                ) >365 
      THEN cast(${created_week} as varchar)

      WHEN 
        datediff(
                'day', 
                cast({% date_start created_date %} as date), 
                cast({% date_end created_date  %} as date)
                ) >30
      THEN cast(${created_date} as varchar)


      WHEN 
        datediff(
                'day', 
                cast({% date_start created_date %} as date), 
                cast({% date_end created_date  %} as date)
                ) >1
      THEN cast(${created_hour} as varchar)      

      
      ELSE ${created_minute}
      END

    ;;


  }

For more info on how to use the date_start and date_end liquid parameters, see [this post] (https://discourse.looker.com/t/using-date-start-and-date-end-with-date-filters/2880).

This dimension will dynamically return a minute, hour, day, or week value, based on the duration of time applied to the ${created_date} dimension. Any Look that uses the time dimension as it’s x-axis will auto-adjust to show an appropriate amount of data based on the filter selection and the rules you’ve built.

Here’s what it looks like live: