You can establish a Looker model that empowers users to select any timeframe, through filters or field selections, and dynamically discover the corresponding start and end dates for each period.
In our example, we will explore how a retailer might compare inventory levels at the beginning and conclusion of specific intervals, such as seasons, quarters, or weeks. Our approach relies on identifying the minimum and maximum dates in the user context (as defined by selected columns and applied filters) and using them to filter the fact table (order_items). This means that selected dimensions from the calendar dimension might determine different beginning and ending dates for the same period. For instance, the following table illustrates a scenario where a retailer aims to contrast the sales figures for the first and final dates of each season over the past 20 months (picture taken the 23rd of September). Notice that by filtering the last 20 months, the winter season of 2023 is incomplete (first date is the 1st of February 2023).
Assume that the table columns are defined as follows:
- Dynamic Calendar Maximum Date: The latest date in the selected period, determined by the dimension and filter.
- Dynamic Calendar Minimum Date: The earliest date in the selected period, determined by the dimension and filter.
- Start Period Sales Price: The total sales amount on the first date of the selected period.
- End Period Sales Price: The total sales amount on the last date of the selected period.
- Order Items Delta: The difference between End Period Sales Price and Start Period Sales Price.
This overview will discuss a public dataset and additional definitions that readers can use to replicate our approach, and then present some key Looker views (Calendar, Dynamic Calendar) and Order Items, with detailed explanations of each dimension and measure. Later, we will bring all the definitions together in a Looker Explore, to provide our business users with an environment for dynamic data analysis.
Data used
The example leverages the public dataset bigquery-public-data.thelook_ecommerce and incorporates a calendar table. The calendar table was derived as follows:
- Generating a calendar table:
CREATE OR REPLACE TABLE thelook_ecommerce.calendar AS
SELECT
date,
FROM UNNEST(GENERATE_DATE_ARRAY('2010-01-01', '2050-12-31')) AS Date
ORDER BY date;
- Create a view that considers as relevant those dates between the first sale event and the current date:
create or replace view thelook_ecommerce.calendar_order_dates as
select calendar.*, concat(extract (year from date),'-',case when extract(month from date) < 7 then 'W' else 'S' end) as season
from thelook_ecommerce.calendar calendar inner join
(
select cast( min(created_at) as date) as min_date, cast(max(created_at) as date) as max_date
from bigquery-public-data.thelook_ecommerce.order_items orders
where cast(created_at as date) <= current_date()
) as order_dates on calendar.date between min_date and max_date
Calendar view
The basic calendar view presents only the date and season objects.
view: calendar {
sql_table_name: `lookerdemos.thelook_ecommerce.calendar_order_dates` ;;
dimension_group: date {
type: time
timeframes: [raw, date, week, month, quarter, year]
convert_tz: no
datatype: date
sql: ${TABLE}.date ;;
}
dimension: season {
type: string
sql: ${TABLE}.season ;;
}
}
Dynamic calendar
The dynamic calendar view is more complex. Let’s start by importing the calendar view previously defined. We will use custom SQL:
include: "/views/**/calendar.view.lkml"
view: dynamic_calendar {
derived_table: {
We build the SELECT statement by including as selected objects the calendar dimension selected by the user. In principle, a user can select multiple time dimensions; however, the most granular will determine the actual dates.
Even if a user does not select a column, we enforce a GROUP BY expression over a placeholder dimension defined as “1”.
Finally, we calculate the minimum and maximum date:
sql: SELECT
{% if calendar.date_year._is_selected %} EXTRACT(YEAR FROM calendar.date ) as year, {% endif %}
{% if calendar.date_month._is_selected %} FORMAT_DATE('%Y-%m', calendar.date ) as month , {% endif %}
{% if calendar.date_quarter._is_selected %} (FORMAT_DATE('%Y-%m', DATE_TRUNC(calendar.date , QUARTER))) as quarter , {% endif %}
{% if calendar.date_week._is_selected %} (FORMAT_DATE('%F', DATE_TRUNC(calendar.date , WEEK(MONDAY)))) as week , {% endif %}
{% if calendar.date_date._is_selected %} (calendar.date ) as date , {% endif %}
{% if calendar.season._is_selected %} (calendar.season ) as season , {% endif %}
1 as placeholder,
min(date) as min_date,
max(date) as max_date
FROM lookerdemos.thelook_ecommerce.calendar_order_dates as calendar
The WHERE condition is built by considering the user condition applied to the calendar dimension. In case a user does not apply any condition, we ensure we will still generate working SQL code with the TRUE expression:
WHERE TRUE
{% if calendar.date_year._is_filtered %} AND {% condition calendar.date_year %} date {% endcondition %} {% endif %}
{% if calendar.date_month._is_filtered %} AND {% condition calendar.date_month %} date {% endcondition %} {% endif %}
{% if calendar.date_quarter._is_filtered %} AND {% condition calendar.date_quarter %} date {% endcondition %} {% endif %}
{% if calendar.date_week._is_filtered %} AND {% condition calendar.date_week %} date {% endcondition %} {% endif %}
{% if calendar.date_date._is_filtered %} AND {% condition calendar.date_date %} date {% endcondition %} {% endif %}
{% if calendar.season._is_filtered %} AND {% condition calendar.season %} season {% endcondition %} {% endif %}
Finally, we generate the GROUP BY statement, which includes user-selected dimensions:
GROUP BY
{% if calendar.date_year._is_selected %} EXTRACT(YEAR FROM calendar.date ) ,{% endif %}
{% if calendar.date_month._is_selected %} FORMAT_DATE('%Y-%m', calendar.date ) ,{% endif %}
{% if calendar.date_quarter._is_selected %} (FORMAT_DATE('%Y-%m', DATE_TRUNC(calendar.date , QUARTER))) ,{% endif %}
{% if calendar.date_week._is_selected %} (FORMAT_DATE('%F', DATE_TRUNC(calendar.date , WEEK(MONDAY)))) , {% endif %}
{% if calendar.date_date._is_selected %} (calendar.date ) , {% endif %}
{% if calendar.season._is_selected %} (calendar.season ) , {% endif %}
1
;;
}
For each calendar object a user can select (in our example, year, quarter, month, week, date, and season) and for the two dates (min and max date), we create a hidden dimension. (We do not want users to use these objects in their reporting.)
dimension: min_date {
hidden: yes
type: date
sql: ${TABLE}.min_date ;;
}
dimension: season {
hidden: yes
type: string
sql: ${TABLE}.season ;;
}
dimension: date {
hidden: yes
type: date
sql: ${TABLE}.date ;;
}
dimension: week {
hidden: yes
type: string
sql: ${TABLE}.week ;;
}
dimension: quarter {
hidden: yes
type: string
sql: ${TABLE}.quarter ;;
}
dimension: month {
hidden: yes
type: string
sql: ${TABLE}.month ;;
}
dimension: year {
hidden: yes
type: number
sql: ${TABLE}.year ;;
}
dimension: max_date {
type: date
sql: ${TABLE}.max_date ;;
}
}
Order items
In addition to dimensions and measures Looker defines when you import the order_items table, we also define the measures start_period_sales_price and end_period_sales_price, which reflect the minimum and maximum dates, respectively:
measure: start_period_sales_price {
type: number
sql: sum( case when ${created_date} = ${dynamic_calendar.min_date} then ${sale_price} end) ;;
}
measure: end_period_sales_price {
type: number
sql: sum( case when ${created_date} = ${dynamic_calendar.max_date} then ${sale_price} end) ;;
}
We also introduce a measure that computes the delta between these two values:
measure: delta {
type: number
sql: ifnull(${end_period_sales_price},0) - ifnull(${start_period_sales_price},0) ;;
}
Explore
Finally, we bring all these definitions together in a single Explore, which is defined in a dedicated model file as follows:
explore: order_items_dynamic_period {
view_name: order_items
join: products {
type: left_outer
sql_on: ${order_items.product_id} = ${products.id} ;;
relationship: many_to_one
}
In our analysis, we incorporate the dynamic_calendar table by joining it based on either the minimum or the maximum date specified by the user’s selections (filters or dimensions). These dates serve as flags to identify relevant dates for our evaluation according to the logic specified in the measures we discussed previously.
To prevent these measures from impacting others, we utilize a left_outer join. However, for enhanced performance, we suggest using an inner join whenever feasible.
join: dynamic_calendar {
type: left_outer
sql_on: ${dynamic_calendar.min_date}=${order_items.created_date} or
${dynamic_calendar.max_date}=${order_items.created_date};;
relationship: one_to_one
}
When considering calendar-based dimensions, we ensure that the connection between our static calendar date and our dynamic calendar is determined by the user analysis granularity (which in turn determines the min and max dates according to the dynamic calendar definition):
join: calendar {
type: inner
relationship: one_to_one
sql_on: TRUE
{% if calendar.date_year._is_selected %} AND ${calendar.date_year} = ${dynamic_calendar.year} {% endif %}
{% if calendar.date_month._is_selected %} AND ${calendar.date_month} = ${dynamic_calendar.month} {% endif %}
{% if calendar.date_quarter._is_selected %} AND ${calendar.date_quarter} = ${dynamic_calendar.quarter} {% endif %}
{% if calendar.date_week._is_selected %} AND ${calendar.date_week} = ${dynamic_calendar.week} {% endif %}
{% if calendar.date_date._is_selected %} AND ${calendar.date_date} = ${dynamic_calendar.date} {% endif %}
{% if calendar.season._is_selected %} AND ${calendar.season} = ${dynamic_calendar.season} {% endif %}
;;
}
}
Start and End… Period
This overview showed how to build a Looker model that lets users choose specific timeframes and compare data from the start and end of those periods. By using a custom calendar and smart coding, you can create a tool that’s both flexible and powerful.
This dynamic approach opens up new possibilities for data exploration and empowers you to find insights you need.