Dynamic Timeframes for Dimension Groups with Parameters

[clrcrl]

Often our business users want to change timeframes on the fly - our internal teams report in weekly cycles while our board want to see things in monthly cycles.

Previously, we had to maintain two sets of dashboards in order to do this.

A few months back I read this discourse article about how to implement this using templated filters.

This week, I implemented a similar solution, but using parameters along with a custom function.

I’ll follow a similar a pattern to what I found in the original article. Please note this is written for Redshift.

Creating dynamic dimensions with a parameter

Let’s assume you have a dimension group with the desired timeframes.

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

Next, let’s set up a string parameter which allows the timeframes we want.

  parameter: date_part {
    type: string
    default_value: "week"
    allowed_value: {
      value: "minute"
    }
    allowed_value: {
      value: "hour"
    }
    allowed_value: {
      value: "day"
    }
    allowed_value: {
      value: "week"
    }
    allowed_value: {
      value: "month"
    }
    allowed_value: {
      value: "quarter"
    }
    allowed_value: {
      value: "year"
    }
  }

Now, let’s make a dimension that uses the DATE_TRUNC function to truncate our date to the desired date part.

dimension: created_date_trunc {
  type: date_time
  sql: DATE_TRUNC({% parameter date_part %}, CONVERT_TIMEZONE('UTC', 'Australia/Sydney', ${created_raw})) ;;
  group_label: "Created Date"
  convert_tz: no
}

A few things to note:

• I had to perform my timezone conversion within my function. As a result, I had to set convert tz: no.
• I’ve used the group_label: parameter to group it up with the other “Created Date” dimensions.
• Because this has type: date_time, values will always display as times (e.g. 2017-10-01 00:00 for the month of 2017-10).

Using a custom function to format strings

Initially we thought we were happy to live with having our months display as times (and you may be!) but we ran into issues with column visualisations, which are frequently used in our organisations (there was excessive white space in column charts as a result).

The other option would be to write a CASE statement as outlined in the original discourse article, however, wherever possible, I try not to repeat myself in code.

Instead I created a custom function that takes a date part input, and formats a time as an appropriate string:

CREATE FUNCTION date_to_string (VARCHAR, DATETIME)
RETURNS VARCHAR
 STABLE
AS $$
  SELECT
  CASE
    WHEN LOWER($1) IN ('year') THEN TO_CHAR($2, 'YYYY')
    WHEN LOWER($1) IN ('quarter', 'month') THEN TO_CHAR($2, 'YYYY-MM')
    WHEN LOWER($1) IN ('week', 'day') THEN TO_CHAR($2, 'YYYY-MM-DD')
    WHEN LOWER($1) IN ('hour', 'minute') THEN TO_CHAR($2, 'YYYY-MM-DD HH24:MI')
  END
$$ LANGUAGE sql

We then updated our dimensions as follows (note the change in the type: and sql: parameters, and the removal of convert_timezone: no)

dimension: created_date_trunc {
  type: string
  sql: DATE_TO_STRING({% parameter date_part %}, DATE_TRUNC({% parameter date_part %}, CONVERT_TIMEZONE('UTC', 'Australia/Sydney', ${created_raw}))) ;;
  group_label: "Created Date"
}

Note: I could probably go one step further and create a DATE_TRUNC_TO_STRING function, which would combine the two functions, but I felt that was overkill.

Accessing the parameter from multiple views

In our organisation, our main use case for these new “date_trunc” fields was to implement them on our major company dashboards. Some of our main metrics are based on our tasks table (for example, the number of tasks each week), while some are based on our users table (for example, the number of new users each week).

It made sense to me to then create the date_part field in a way that was accessible by a number of different views. The best solution I came up with in terms of flexibility was to put this field in its own view, called parameters.

view: parameters {
  parameter: date_part {
    ...  # as above
  }
}

I then joined this view onto each explore, as follows:

explore: users {
   ...
   join: parameters {}
 }

And of course, updated my new fields to reference {% parameter parameters.date_part %} instead of {% parameter date_part %}.

Benefits

Overall I found creating a custom function to be a better solution than writing out the CASE statement (as illustrated in this article) because the code is significantly simpler.

Making the date_part parameter exist in its own view means that I won’t clutter my Looker field picker (or confuse my users!)

There you have it! Hope that someone finds this useful 🙂

[katie1]

Hi,

I’m trying to set up the same type of dynamic timeframe selector as you describe here (also using Redshift). One issue I’ve run into is that the filter on our formatted date field (the equivalent of your ‘created_date_trunc’ field) provides options for filtering strings, rather than date ranges. This makes sense since our formatted date field is forced to strings to created the correct display formatting. Did you happen to find a good work-around for this issue?

Thanks!