Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Timelines with dynamic date granularity

  • 6 September 2017
  • 3 replies
  • 1827 views
zlebowitz
Rank
Userlevel 3

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:

3 replies

brayden
Userlevel 3

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

zlebowitz
Rank
Userlevel 3

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

;;}

Reply


Terms of Service