Date scale switching on charts (switch between weekly, monthly, etc. on the fly)

Been waiting for looker to come out with this feature but couldn’t wait any longer. You can use parameters and some logic on a date dimension to allow a drop down filter to modify the time scale on any chart. This doesn’t change the actual time scale but it modifies dates on records to group them together based on the month/day/year/etc. Go from monthly to weekly to daily in a couple clicks without having to edit the chart in an explore.

This is made possible by using parameters and liquid variables in the sql section of my desired dynamic date dimension. It also required that your flavor of sql has functions to return day of week, day of month, day of year, etc. We use Snowflake so that’s what you’ll see here:

parameter: date_part {

    type: unquoted

    allowed_value: {

      label: "Years"

      value: "DAYOFYEAR"

    }

    allowed_value: {

      label: "Weeks"

      value: "DAYOFWEEK"

    }

    allowed_value: {

      label: "Months"

      value: "DAYOFMONTH"

    }

  }



  dimension: responded_dynamic_date {

    type: date

    sql: DATEADD(d, (-1 * {% parameter date_part %}(${TABLE}.date_field) + 1), ${TABLE}.date_field) ;;

  }