Dynamic Dates with Liquid

  • 4 August 2020
  • 6 replies
  • 4773 views

Userlevel 2

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.


6 replies

Userlevel 7
Badge +1

Will we ever be able to create local parameters in queries/looks/dashboards just by using Custom Fields?

Userlevel 1

Thank you!

Is it possible to drill from a look/tile that uses the dynamic date dimension to another dashboard that gets filtered by the dynamic date (I am having trouble to get this to work)?

 

extract_months(${orders.create_date}) <= extract_months(now())-1) AND ${orders.create_date} > add_months(-6,add_months(-1,now()))

How to achieve this below kind of subtotal of every 2 rows beneath them?

  Price Count Total Price
Product A 1 2 3
Product B 1 2 3
Subtotal A+B A1+B1 A2+B2 A3+B3
Product C 1 2 3
Product D 1 2 3
Subtotal C+D C1+D1 C2+D2 C3+D3
Product E 1 2 3
Product F 1 2 3
Subtotal E+F E1+F1 E2+F2 E3+F3

how can we reference the manifest and parameter files in the view for the above solution to work?

Regarding the first gotcha mentioned by the OP :



The first is that it is a type string, this means that we cannot filter on this dimension like a normal date.

 

If someone is looking for a quick work-around then the easiest thing to do is to use the original date dimension for filtering. 

 

It’ll work even if the original date dimension (group) is not explicitly added in the charts in the dashboard. Charts in the dashboard can use the dynamic dimension (to let the timeframe picker do its magic) while the dashboard filter uses the original date dimension.

 

Based on the original example, charts should use Created Date Dynamic and the filter should use Created Date.

Reply