Compare 2 arbitrary periods in Looker

Knowledge Drops
1

Daasity provides you with some handy out-of-the-box ways to compare time periods, but they may not meet all of your team’s needs.

One thing your team may want to do is to compare results from 2 arbitrary periods. We don’t include this out of the box because it’s not very intuitive in Looker, but this article will walk through how you can set this up.

(Note: We’re going to walk through the method to do this in the hub-and-spoke setup that our newer merchants are using. If you’re an Enterprise customer that has come on board before September 2023, you will need to tweak these steps.)

In this process, you’re going to add some filters & dimensions to the retail_calendar view via refinements.

Step 1: Create a refinement file
Create a new generic LookML file in the base_refinements folder. You can name it whatever you like, but in this example we’ll name it period_analysis.lkml:
image


Step 2: Add refinement contents
Add the following code to the refinement file:

include: "//base_daasity/views/drp/calendar.view.lkml"

view: +retail_calendar {

#
# FILTERS
#

  filter: first_period_filter {
    view_label: "Calendar - Custom Comparisons"
    label: "First Period"
    description: "Choose the first date range to compare against. This must be before the second period"
    type: date
    convert_tz: no
  }

  filter: second_period_filter {
    view_label: "Calendar - Custom Comparisons"
    label: "Second Period"
    description: "Choose the second date range to compare to. This must be after the first period"
    type: date
    convert_tz: no
  }

#
# DIMENSIONS
#

  dimension: period_selected {
    view_label: "Calendar - Custom Comparisons"
    label: "Period"
    description: "Select for Comparison (Pivot)"
    type: string
    sql:
      CASE
        WHEN {% condition first_period_filter %} ${calendar_date} {% endcondition %} THEN 'First Period'
        WHEN {% condition second_period_filter %}${calendar_date} {% endcondition %} THEN 'Second Period'
      END 
      ;;
  }

#
# HIDDEN DIMENSIONS
#

  dimension: days_from_first_period {
    hidden: yes
    view_label: "Calendar - Custom Comparisons"
    description: "Select for Grouping (Rows)"
    type: number
    sql:
      CASE
        WHEN ${days_from_start_second} >= 0 THEN ${days_from_start_second}
        WHEN ${days_from_start_first} >= 0 THEN ${days_from_start_first}
      END
      ;;
  }

  dimension: days_from_start_first {
    view_label: "Calendar - Custom Comparisons"
    hidden: yes
    type: number
    sql: DATEDIFF('day',  {% date_start first_period_filter %}, ${calendar_date}) ;;
  }

  dimension: days_from_start_second {
    view_label: "Calendar - Custom Comparisons"
    hidden: yes
    type: number
    sql: DATEDIFF('day',  {% date_start second_period_filter %}, ${calendar_date}) ;;
  }

}



Step 3: Deploy your changes
Push your changes to production. The new filters and dimensions will now show up wherever you already are using the Calendar view.


Step 4: Do some analysis!
Here’s a demo of how to use these new fields:





If you still have other comparison period needs, here’s some further reading: