Dynamic Dates with Liquid

[sam_pitcher]

It is common for date/time series tiles in a dashboard to be easily switched between date/week/month etc. This can be done with a parameter and liquid in the LookML.

It is good to have a separate view file called parameters.view, maybe saved in a common_views folder, where parameters can be defined. In this example we will create a parameter called timeframe_picker in our parameters.view file.

view: parameters {
  extension: required

  #####################
  # COMMON PARAMETERS #
  #####################

  # Timeframe

  parameter: timeframe_picker {
    view_label: "-- Parameters"
    label: "Date Granularity"
    type: unquoted
    allowed_value: { value: "Day" }
    allowed_value: { value: "Week" }
    allowed_value: { value: "Month" }
    allowed_value: { value: "Year" }
    default_value: "Day"
  }

Note that the view has no sql_table_name, but instead has extension: required. We can then extend this parameter view into other view files instead of having to recreate a timeframe_picker parameter in each.

In our view file that has the date dimension, we can create a new dimension and apply some liquid.

  dimension_group: created {
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.created_date ;;
  }

  dimension: created_date_dynamic {
    type: string
    description: "Use with timeframe picker to change date granularity"
    sql: 
      {% if timeframe_picker._parameter_value == 'Day' %} ${created_date}
      {% elsif timeframe_picker._parameter_value == 'Week' %} ${created_week}
      {% elsif timeframe_picker._parameter_value == 'Month' %} ${created_month}
      {% elsif timeframe_picker._parameter_value == 'Year' %} ${created_year}
      {% else %} null {% endif %} ;;
  }

This will work great for reporting with. However there are a few gotchas.

The first is that it is a type string, this means that we cannot filter on this dimension like a normal date. The second is this still requires all the timeframes to be outlined for the base dimension_group and this can bulk out the Explore if you have many dates.

A nice way to handle this is perhaps to limit the timeframes down to just date in the base dimension group and then have the dynamic date that is used for dashboards and reporting.

To do this in a DRY way, we can create two constants in a manifest file, that we can use around our code:

constant: dynmamic_date_sql {
  value: "{% assign dimension_name = _field._name | replace: '_dynamic', '' %}
         {% if timeframe_picker._parameter_value == 'Day' %} {{ dimension_name }}
         {% elsif timeframe_picker._parameter_value == 'Week' %} DATE_TRUNC({{ dimension_name }}, WEEK)
         {% elsif timeframe_picker._parameter_value == 'Month' %} DATE_TRUNC({{ dimension_name }}, MONTH)
         {% elsif timeframe_picker._parameter_value == 'Year' %} EXTRACT(YEAR from {{ dimension_name }})
         {% else %} null
         {% endif %}"
}

constant: dynamic_date_html {
  value: "{% assign month_size = rendered_value.size | minus: 3 %}
         {% if timeframe_picker._parameter_value == 'Month' %} {{ rendered_value | slice: 0, month_size }}
         {% else %} {{ rendered_value }}
         {% endif %}"
}

For this to work, the dynamic date needs to be called the name of the column in the table and then _dynamic. As shown below we have a dimension_group called created, but the column is created_date in the table. Therefore our dynamic date is called created_date_dynamic.

This way, we can just copy the sql and html with the constants shown below and the timeframe_picker will work with them. (NOTE: the sql in the dynmamic_date_sql constant is for BigQuery)

We still can’t filter on this value, but we can apply can_filter: no to the dimension. Then for each date in the Explore, you will have the date (which you can filter normally with) and then the dynamic date that changes from the timeframe_picker.

  dimension_group: created {
    group_label: "Dates"
    type: time
    timeframes: [date]
    sql: ${TABLE}.created_date ;;
  }

  dimension: created_date_dynamic {
    label_from_parameter: timeframe_picker
    group_label: "Dates"
    description: "Use with timeframe picker to change date granularity"
    can_filter: no
    sql: @{dynmamic_date_sql} ;;
    html: @{dynamic_date_html} ;;
  }

The reason you can’t have this in one dimension, is because with dimension groups the sql in the where clause is different to what is in the select statement.