How-to: This Year vs. Last Year Comparisons in Looker
Originally authored for Looker 6.16.16
Business Requirement:
Your business users have requested the ability to show both a ‘this year’ and the corresponding ‘last year’ date/date range on a single report. The standard Looker filtering behavior does not allow you to query and display both result sets in a single report. The use case is further complicated by your company’s use of a custom calendar (as in, last_year != date_sub(this_year, interval 1 year) ).
Technical Pre-Requisites:
This write up is intended for a technical audience. The write up will assume a strong understanding of LookML and the following structure:
Model file: view.sales is joined to view.calendar on view.sales.date = view.calendar.date in a many-to-one relationship
Looker views: view.sales and view.calendar
Database tables: table.sales_fact and table.calendar_lookup
Assumption:
The table.calendar_lookup mentioned above is keyed on the ‘this year’ calendar date. The table also contains a separate column which explicitly states the corresponding ‘last year’ date in a 1:1 relationship. When building the calendar table, calculating all dates for several years into the future is time well spent!
Although it is possible to expose multiple date forms by applying the timeframes: […] command on an existing date column, the inclusion of a separate view.calendar allows greater flexibility in date-based calculations and expressions. This configuration also allows you to re-use date logic in multiple explores as the project becomes more complex. Outcome: Consistent user experience and the most concise code base.
Step 1: Setting up your view.calendar
Begin by declaring the view.calendar, assigning a primary key, and declaring the this year and last year date dimensions. A sample of this set up is included below:
view: calendar {
view_label: "Calendar Dimensions"
derived_table: {
datagroup_trigger: datagroup_midnight
sql_table_name: table.calendar_lookup ;;
}
dimension: calendarpk {
hidden: yes
type: string
sql: cast(${TABLE}.this_year_date as timestamp) ;;
}
dimension_group: calendar_date_date {
timeframes: [date]
type: time
datatype: date
sql: cast(${TABLE}.this_year_date as date) ;;
convert_tz: no
}
dimension: last_year_date {
hidden: yes
type: string
sql: ${TABLE}.calendardatelastyear ;;
}
dimension_group: calendar_date {
timeframes: [week, month, year, fiscal_quarter, raw]
type: time
sql: select cast(${TABLE}. this_year_date as timestamp), cast(${TABLE}.last_year_date as timestamp) ;;
convert_tz: no
}
Step 2: Inserting Logic to Support This Year and Last Year
Several logical components are required. These components are written into the view.calendar and several are hidden to avoid confusing end-users. A stand-alone date filter object is required to enable the TY / LY functionality. The code sample below contains some liquid parameters. If you’re uncomfortable with this syntax, please read more about liquid through this link
filter: date_range {
label: "TY / LY Filter"
description: "Pick only TY date range. LY calc'ed automatically."
type: date
}
dimension: is_in_date_range {
hidden: yes
type: yesno
sql: {% condition date_range %} CAST(${calendar_date_date_date} AS TIMESTAMP) {% endcondition %} ;;
}
dimension: is_in_previous_year_daterange {
hidden: yes
type: yesno
sql:{% condition date_range %} CAST(${last_year_date} AS TIMESTAMP) {% endcondition %} ;;
}
dimension: calendar_last_year_date_for_tyly {
hidden: yes
type: date
sql: ${calendar_last_year.calendar_date_date} ;;
}
dimension: ty_ly {
label: "TY / LY"
type: string
alpha_sort: no
sql: CASE WHEN ${is_in_date_range} THEN "This Year"
WHEN ${is_in_previous_year_daterange} THEN "Last Year"
ELSE "Out of Range" END;;
}
Step 3: Modify the model file to Create the ‘last year’ Case
After the relevant objects have been created in the view.calendar, join your new objects through the model file. The code sample below shows view.sales joined to view.calendar TWICE.
explore: sales {
sql_always_where: {% if calendar.date_range._is_filtered %}
${calendar.ty_ly} != 'Out of Range'
{% else %} 1=1 {% endif %} ;;
}
join: calendar {
relationship: many_to_one
type: left_outer
sql_on: ${sales.dateastimestamp} = ${calendar.calendarpk} ;;
}
join: calendar_last_year {
fields: []
from: mastercalendarxref
relationship: many_to_one
type: left_outer
sql_on: ${sales.date_as_timestamp} = cast(${calendar_last_year.last_year_date} as timestamp) ;;
}
# Other joins as normal to complete your explore …
}
Some important notes about the code block above:
The sql_always_where at the top of the explore is required so your TY / LY dimension returns only 2 distinct cases.
The fields: [] parameter is necessary so your date field does not duplicate within the explore. Although the code would work without this exclusion, it is important to include this parameter to provide the cleanest possible user experience.
When joining, it is always necessary to match your data types in the sql_on: parameter. A cast( command is convenient here. See join: calendar_last_year above for an example.
Step 4: IT Testing, UAT, Deployment to Production, etc.
Step 5: Change Management & End-User Training
This is a fairly advanced change for non-technical users to understand. Meet with users and demonstrate the new functionality in several different reporting circumstances. If possible, build the TY / LY filtering into several existing reports so that users get comfortable with reading comparative reports before having to apply it on their own. You can use table calculations to produce TY / LY Percent Change measures.
As an application owner, don’t be surprised if users start asking questions about last year’s data. It’s not uncommon for end-users to cry foul because of events that have faded from memory (example: a hurricane slowed business, a large public event near your store drove incredible business, a new product release caused a short spike in demand, etc.).
Good luck!
