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:
So smart! Can you repost the gif showing the before/after filtering?
Thanks for the note, @brayden. I didn’t realize the original gif got cut short. Should be good now!
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
;;}