Advanced Date Fields
The Problem
Looker has many out-of-the-box tools for wrangling dates. When defining a dimension group in Looker, you can pick between several ‘timeframes’, which allow users to easily group their data. However, Looker has a blindspot for more dynamic date groupings. What if a user wants to group the data into 15 day or 3 week buckets? We can compare quarter over quarter performance, but while comparing the last quarter to the current incomplete quarter, we find that we’re comparing apples to oranges. For the purposes of tracking pacing, it’s much more intuitive to compare the number of days that have elapsed so far in this quarter to the same number of days from the start of the previous quarter. The finance team often wants to be able to change the day that a week starts on (E.g., Tues-Mon vs. Sun-Sat). Logical complexity aside, this project posed an additional problem. More robust date fields were a request we had received from just about every team. The solution to this problem needed to be compatible with, and update all existing Explores in our instance.
How I Solved It
I created a view which could be joined on to all of our existing explores. This view generated a calendar of dates with one row per date, and various date formats for potential future use cases. I then created dimensions in LookML which featured parameters to allow the user to customize these fields. For example, the rolling date buckets included a parameter for the interval of time (days, weeks, etc.), and another parameter for the number of intervals in each bucket. These fields folded in the logic necessary to ensure that these fields accounted for all possible parameter values. To the user, parameters appear in the same place as a normal date filter, which made adoption surprisingly intuitive. As an added benefit, my solution did not require querying from a table in my database, but instead from a logic-generated calendar. If you’re interested in deploying this solution in your own environment, you can migrate my code to your instance (Trino Syntax).
Here’s a Snapshot of what a 5 day window would look like:
The Derived Table
Knowing that I was planning on joining this view onto all existing explores, I needed to ensure that this addition would have a negligible effect on performance. The most bombproof way to accomplish this was to ensure that any join would have at worst a one-to-many relationship by creating a calendar exploder, and using this generated calendar as the derived SQL for my view. This ensured that the view did not have any dependencies on other tables, but more importantly that I could guarantee that each date would only appear once in a single row. The resulting table was a clean and minimal sequence of dates which could easily be joined onto any explore without slowing it down.
SELECT
CAST(date_column AS DATE) AS calendar_date
, CONCAT(CAST(date_column AS VARCHAR), 'T00:00:00Z') AS calendar_dt
, DATE_TRUNC('quarter', CAST(date_column AS DATE)) AS calendar_qtr
, DATE_TRUNC('month', CAST(date_column AS DATE)) AS calendar_month
FROM (VALUES (SEQUENCE(CAST('2013-01-01' AS date), CAST(NOW() AS date) + INTERVAL '3' month, INTERVAL '1' day))) AS t1(date_array)
CROSS JOIN UNNEST(date_array) AS t2(date_column)
;;
The LookML
The LookML for the dynamic date buckets (15 days, 2 weeks, etc.). This field ingests 3 parameters, which are listed as required fields, and uses them as variables which the user can substitute. If we imagine that the user selected an N value of 2 and an interval of “weeks”, we would receive rolling 2 week date buckets. For each date on or before the selected anchor date, the field returns the first day of the two-week bucket in which that date lives, counting backwards from the selected anchor date. It does so by computing how many whole two‑week periods separate the value of calendar date from the anchor date, and uses that count to subtract the right number of weeks from the anchor. It then shifts forward one day to mark the bucket’s start, and returns that date.
dimension: rolling_n {
label: "Last Trailing n Window 📅️"
label_from_parameter: rolling_n_timeframe
description: "Creates date groups of days/weeks/months/quarters/years using your input from --> 'Lookback Anchor Date' 'N' and 'Date Granularity'.
E.g., Rolling 3 week buckets trailing from 2025-02-15."
required_fields: [rolling_n_param, rolling_n_start_param, rolling_n_timeframe]
sql:
CAST(
CASE
WHEN ${TABLE}.calendar_date > {{ rolling_n_start_param._parameter_value }} THEN NULL
ELSE DATE_ADD(
'day'
, 1
, DATE_ADD(
{{ rolling_n_timeframe._parameter_value }}
, -1 * (
(
FLOOR(
DATE_DIFF(
{{ rolling_n_timeframe._parameter_value }}
, ${TABLE}.calendar_date
, {{ rolling_n_start_param._parameter_value }}
)
)
/ {{ rolling_n_param._parameter_value }}
)
+ 1
)
* {{ rolling_n_param._parameter_value }}
, {{ rolling_n_start_param._parameter_value }}
)
)
END AS DATE
)
;;
}