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:

[brayden]

So smart! Can you repost the gif showing the before/after filtering?

[zlebowitz]

Thanks for the note, @brayden. I didn’t realize the original gif got cut short. Should be good now!

[aosborn]

This looks great. I did a modification to work with SQL Azure syntax (and my own chart properties) if this is useful to others looking to do the same. After lots of head banging I realised SQL Azure had subtle differences in how the start and end date params should be referenced.

 dimension_group: filter_start_date {
type: time
timeframes: [raw,date]
sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '1970-01-01'
ELSE CAST({% date_start date_filter %} AS DATE)
END;;}

  dimension_group: filter_end_date {
type: time
timeframes: [raw,date]
sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CAST(CURRENT_TIMESTAMP AS DATE)
ELSE CAST({% date_end date_filter %} AS DATE)
END;; }

 dimension: time {
sql:
CASE
  WHEN
      (DATEDIFF(DAY, ${filter_start_date_raw}, ${filter_end_date_raw})) >90
  THEN cast(${created_month_name} as varchar)
  WHEN
      (DATEDIFF(DAY, ${filter_start_date_raw}, ${filter_end_date_raw})) >60
  THEN cast(${created_week} as varchar)
  WHEN
      (DATEDIFF(DAY, ${filter_start_date_raw}, ${filter_end_date_raw})) >2
  THEN cast(${created_date} as varchar)
  ELSE ${created_hour}
  END
;;}