Dynamically change visualization depending on date filter selected

[danidow]

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 · Accepted solution]

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!

[danidow]

Thank you! This worked, the only thing I needed to change was to enclose the value of the parameter in the sql in both double and single quotes. I found this from https://cloud.google.com/looker/docs/reference/param-field-parameter#parameters_of_type_string

and this is what the dimension looked like:

dimension: dynamic_insert_date {
type: string
sql:
{% if date_granularity._parameter_value == "'hourly'" %}
${insert_date_hour}
{% elsif date_granularity._parameter_value == "'daily'" %}
${insert_date_date}
{% else %}
NULL
{% endif %};;
}

It would be nice for it to automatically change depending on the date filter selection, but at least this way the user can see the hourly breakdown even for multiple days if they would like. 

[sam8]

Good catch on the extra quoting, and thanks for sharing! 😁

[blinton_exim]

how can i contact you i have an idea to discuss