Solved: Dynamically change visualization depending on date... - Page 2

danidow · 03-24-2024 11:16 AM

I have a Looker dashboard with a date filter and some visualizations. One of the visualizations is a bar chart showing some measures for each date in the range. I would like to show an hourly breakdown if the user selects a single day on the date filter and if they select a range of dates to show a daily breakdown. The date column selected for the visualization is currently the date option from a dimension_group with the LookML defined like this:

dimension_group: insert_date {
type: time
timeframes: [hour,date,week,month,year,raw]
convert_tz: no
datatype: datetime
sql: ${TABLE}.insert_date ;;
}

The filter is currently selected as "Insert Date Date" and Control set to "Advanced".

I've done a lot of reading about parameters and templated filters and cannot figure out how to do this. Any help would be appreciated!

sam8

That is a super cool use case!

You can change the breakdown from hourly to daily, but not based on what the user selects in the date filter. You'd need to create a separate parameter field that toggles the breakdown. In fact, your use case is very similar to the Creating logical statements with parameter_name._parameter_value example!

First, create a parameter with two options for the user to select:

parameter: date_granularity {
  type: string
  allowed_value: {
    label: "Hourly"
    value: "hourly"
  }
  allowed_value: {
    label: "Daily"
    value: "daily"
  }
}

Then, create a dimension which listens to that parameter and returns the appropriate timeframe:

dimension: dynamic_insert_date {
  type: string
  sql:
    {% if date_granularity._parameter_value == 'daily' %}
      ${insert_date}
    {% elsif date_granularity._parameter_value == 'hourly' %}
      ${insert_hour}
    {% else %}
      ${created_date}
    {% endif %};;
}

Next, go into each of your visualizations on the dashboard and replace the instances of "insert_date" with "dynamic_insert_date".

Finally, add the "date_granularity" parameter as a dashboard filter. Now, users can select "Hourly" or "Daily" to change the date granularity for all visualizations on the dashboard.

I hope this helps!

View solution in original post

Dynamically change visualization depending on date filter selected