Date Comparison Block

blocks
(Ben Cannon) #1

Date Comparison Block

What is this block?

This block allows for comparisons from one date period to another, or from one date period to a number of periods. It provides extensive flexibility in comparing date ranges, and presenting these back as a single visualisation. The code can be found in this repo in the Datatonic GitHub.

Motivation

There are already a few blocks that allow for comparison between periods in Looker. The motivation for this block was to combine all the elements from the other blocks in a way that is intuitive, flexible and fast to use.

Solution

The approach was to use the intuitive style of date comparison that is used in Google Analytics - where the basic idea is that you choose the current date range that you’re interested in, then choose the comparison date range in the past. There are some templated period filters to make this easy for the user, but also custom filters to allow flexibility in comparing dates. The features are:

  1. Templated comparison periods - Once the initial period of interest is chosen, rather than manually choose the period before, there is a range of options presented such as “Previous Period”, “Previous Month”, “Previous Year” etc

  2. Custom comparison periods - in the situation where any of the above don’t fit what you are trying to see, then a custom previous date range may be chosen

  3. Any granularity - Choose how granular you want your results to be by picking the appropriate date dimension from the dimension group

  4. Multiple periods - Choose the number of periods you would like to compare, this is only available for templated periods, e.g January this year vs January last year vs January 2 years ago etc.

How to use

The process for using this in your explore is as follows:

  1. Add the filter Date Range to choose your initial date range
  2. Add the filter Compare To (Templated) to choose a templated comparison range OR Add the filter Compare To (Custom) to choose a custom comparison range
  3. If you have chosen a templated range, you may choose to add more periods by using the Comparison Periods filter
  4. Choose your date dimension - only pick from the dimension group Current Period Date, don’t use any date dimension from any other view
  5. Choose your other dimensions and measures as usual
  6. Finally, pivot on Period
  7. Hit run

How to implement

Syntax assumes a BigQuery connection, you may need to adjust this for other database connections. To have this available to use in your explores and dashboards there are a few steps:

  1. Copy the view file _date_comparison.view.lkml into your project
  2. In the view file where the date dimension you would like to be able to compare is, extend the _date_comparison view by adding the parameter extends: [_date_comparison]
  3. In the same view file, add two new dimensions, event_date and event_raw.These are simply <your_date_dimension>_date and <your_date_dimension>_raw respectively. This step is just so that naming convention used in the _date_comparison view works correctly
  4. In the relevant explore LookML, add in the sql_always_where clause defined in the model file here. Replace all instances of <your_view_name> with your view name.
6 Likes

The Podium — May 22nd, 2019
#2

Thanks for great post! @bencannon, can you please also upload pics again? All of them are broken unfortunately.

0 Likes

(Ben Silverstein) #3

Does anyone know how to handle the

DATE({% date_end current_date_range %})

in MS SQL 2012’s dialect?

0 Likes

(Ben Cannon) #4

Thanks! Yes just reuploaded them, had some issues displaying them on github for some reason. Can’t upload them to this post as I’m limited to 1 picture per post as a new user.

2 Likes

(Ben Cannon) #5

Hey Ben, so the bit within the brackets will just return a timestamp relating to the end of the the current_date_range filter (thats “1. Date Range” in the field picker). This is just to convert that timestamp to a date using BQ’s DATE() function - I’d imagine ms sql would be the same if not very similar.

1 Like

(Jamie Fry) #6

Just posting some examples of using this in the explore:

Using templated and number of periods

Using custom with date ranges that are not the same lengh

Templated & matches (advanced)

2 Likes

(Izzy) #7

That didn’t seem right, given the quality of this post, so I bumped you up manually! Welcome, Ben :slight_smile: Thanks for sharing this.

1 Like

(Ben Silverstein) #8

Thanks so much Ben - I got it going now. I’ve got to say - this is absolutely fantastic. I love how lean the code is too. If anyone wants, I can post the MS SQL dialect version of this.

2 Likes

#9

Would be great, thanks in advance!

0 Likes

(Ben Silverstein) #10

For _date_comparison.view.lkml for MS SQL:

# this is the code for the date comparison tool, which mimics what google 360 does in the browser in comparing two different date ranges. use with _date_dim.view.lkml
view: _date_comparison {
  extension: required
  filter: current_date_range {
    view_label: "Timeline Comparison Fields"
    label: "1. Date Range"
    description: "Select the date range you are interested in using this filter, can be used by itself. Make sure any filter on Event Date covers this period, or is removed."
    type: date
  }
  filter: previous_date_range {
    view_label: "Timeline Comparison Fields"
    label: "2b. Compare To (Custom):"
    group_label: "Compare to:"

    description: "Use this if you want to specify a custom date range to compare to (limited to 2 comparison periods). Always use with '1. Date Range' filter (or it will error). Make sure any filter on Event Date covers this period, or is removed."

    type: date
  }

  dimension: days_in_period {
    description: "Gives the number of days in the current period date range"
    type: number
    sql: DATEDIFF(day, {% date_start current_date_range %}, {% date_end current_date_range %}) ;;
    hidden:  yes
  }

  dimension: period_2_start {
    description: "Calculates the start of the previous period"
    type: date
    sql:
    {% if compare_to._in_query %}
      {% if compare_to._parameter_value == "Period" %}
        DATEADD(day, -1*${days_in_period},{% date_start current_date_range %} )
      {% else %}
        DATEADD({% parameter compare_to %},-1,{% date_start current_date_range %} )
      {% endif %}
    {% else %}
      {% date_start previous_date_range %}
    {% endif %};;
    hidden:  yes
  }

  dimension: period_2_end {
    description: "Calculates the end of the previous period"
    type: date
    sql:
    {% if compare_to._in_query %}
      {% if compare_to._parameter_value == "Period" %}
        DATEADD(day,-1,{% date_start current_date_range %})
      {% else %}
        DATEADD({% parameter compare_to %},-1,DATEADD(day,-1,{% date_end current_date_range %} ) )
      {% endif %}
    {% else %}
      {% date_end previous_date_range %}
    {% endif %};;
    hidden:  yes
  }

  dimension: period_3_start {
    description: "Calculates the start of 2 periods ago"
    type: date
    sql:
    {% if compare_to._parameter_value == "Period" %}
        DATEADD(day,-2*${days_in_period},{% date_start current_date_range %})
    {% else %}
        DATEADD({% parameter compare_to %},-2,{% date_start current_date_range %}  )
    {% endif %};;
    hidden: yes

  }

  dimension: period_3_end {
    description: "Calculates the end of 2 periods ago"
    type: date
    sql:
    {% if compare_to._parameter_value == "Period" %}
      DATEADD(day,-1,${period_2_start})
    {% else %}
      DATEADD({% parameter compare_to %},-2,DATEADD(day,-1,{% date_end current_date_range %}) )
    {% endif %};;
    hidden: yes
  }

  dimension: period_4_start {
    description: "Calculates the start of 4 periods ago"
    type: date
    sql:
    {% if compare_to._parameter_value == "Period" %}
        DATEADD(day,-3*${days_in_period},{% date_start current_date_range %} )
    {% else %}
        DATEADD({% parameter compare_to %},-3,{% date_start current_date_range %} )
    {% endif %};;
    hidden: yes
  }

  dimension: period_4_end {
    description: "Calculates the end of 4 periods ago"
    type: date
    sql:
      {% if compare_to._parameter_value == "Period" %}
      DATEADD(day,-1,${period_2_start})
      {% else %}
      DATEADD({% parameter compare_to %},-3,DATEADD(day,-1,{% date_end current_date_range %}) )
      {% endif %};;
    hidden: yes
  }

  parameter: compare_to {
    description: "Choose the period you would like to compare to. Must be used with Current Date Range filter"
    label: "2a. Compare To (Templated):"
    type: unquoted
    allowed_value: {
      label: "Previous Period"
      value: "Period"
    }
    allowed_value: {
      label: "Previous Week"
      value: "Week"
    }
    allowed_value: {
      label: "Previous Month"
      value: "Month"
    }
    allowed_value: {
      label: "Previous Quarter"
      value: "Quarter"
    }
    allowed_value: {
      label: "Previous Year"
      value: "Year"
    }
    default_value: "Period"
    view_label: "Timeline Comparison Fields"
  }

  parameter: comparison_periods {
    label: "3. Number of Periods"
    description: "Choose the number of periods you would like to compare - defaults to 2. Only works with templated periods from step 2."
    type: unquoted
    allowed_value: {
      label: "2"
      value: "2"
    }
    allowed_value: {
      label: "3"
      value: "3"
    }
    allowed_value: {
      label: "4"
      value: "4"
    }
    default_value: "2"
    view_label: "Timeline Comparison Fields"
  }

  dimension: period {
    view_label: "Timeline Comparison Fields"
    label: "Period"
    description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period', 'Previous Period' or '3 Periods Ago'"
    type: string
    order_by_field: order_for_period
    sql:
       {% if current_date_range._is_filtered %}
         CASE
           WHEN {% condition current_date_range %} ${event_raw} {% endcondition %}
           THEN 'This {% parameter compare_to %}'
           WHEN ${event_date} between ${period_2_start} and ${period_2_end}
           THEN 'Last {% parameter compare_to %}'
           WHEN ${event_date} between ${period_3_start} and ${period_3_end}
           THEN '2 {% parameter compare_to %}s Ago'
           WHEN ${event_date} between ${period_4_start} and ${period_4_end}
           THEN '3 {% parameter compare_to %}s Ago'
         END
       {% else %}
         NULL
       {% endif %}
       ;;
  }

  dimension: order_for_period {
    hidden: yes
    view_label: "Timeline Comparison Fields"
    label: "Period"
    description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period', 'Previous Period' or '3 Periods Ago'"
    type: string
    sql:
       {% if current_date_range._is_filtered %}
         CASE
           WHEN {% condition current_date_range %} (${event_raw}) {% endcondition %}
           THEN 1
           WHEN ${event_date} between ${period_2_start} and ${period_2_end}
           THEN 2
           WHEN ${event_date} between ${period_3_start} and ${period_3_end}
           THEN 3
           WHEN ${event_date} between ${period_4_start} and ${period_4_end}
           THEN 4
         END
       {% else %}
         NULL
       {% endif %}
       ;;
  }

  dimension_group: date_in_period {
    description: "Use this as your date dimension when comparing periods. Aligns the all previous periods onto the current period"
    label: "Current Period"
    type: time
    sql: DATEADD(day,${day_in_period}-1,{% date_start current_date_range %} ) ;;
    view_label: "Timeline Comparison Fields"
    timeframes: [date, week, month, quarter, year]
  }

  dimension: day_in_period {
    description: "Gives the number of days since the start of each periods. Use this to align the event dates onto the same axis, the axes will read 1,2,3, etc."
    type: number
    sql:
    {% if current_date_range._is_filtered %}
      CASE
        WHEN {% condition current_date_range %} (${event_raw}) {% endcondition %}
        THEN DATEDIFF(day, {% date_start current_date_range %},${event_date})+1

        WHEN ${event_date} between ${period_2_start} and ${period_2_end}
        THEN DATEDIFF(day,${period_2_start},${event_date})+1

        WHEN ${event_date} between ${period_3_start} and ${period_3_end}
        THEN DATEDIFF(day,${period_3_start},${event_date})+1

        WHEN ${event_date} between ${period_4_start} and ${period_4_end}
        THEN DATEDIFF(day,${period_4_start},${event_date})+1
      END

    {% else %} NULL
    {% endif %}
    ;;
    hidden: yes
  }

}
2 Likes

(Ben Silverstein) #11

To add to your base explore’s SQL_ALWAYS_WHERE for MS SQL:

 {% if your_view_name.current_date_range._is_filtered %}
    {% condition your_view_name.current_date_range %} (${event_date}) {% endcondition %}

    {% if your_view_name.previous_date_range._is_filtered or your_view_name.compare_to._in_query %}
      {% if your_view_name.comparison_periods._parameter_value == "2" %}
      or
      ${event_date} between ${period_2_start} and ${period_2_end}

      {% elsif your_view_name.comparison_periods._parameter_value == "3" %}
        or
        ${event_date} between ${period_2_start} and ${period_2_end}
        or
        ${event_date} between ${period_3_start} and ${period_3_end}


      {% elsif your_view_name.comparison_periods._parameter_value == "4" %}
        or
        ${event_date} between ${period_2_start} and ${period_2_end}
        or
        ${event_date} between ${period_3_start} and ${period_3_end}
        or
        ${event_date} between ${period_4_start} and ${period_4_end}

      {% else %} 1 = 1
      {% endif %}
    {% endif %}
  {% else %} 1 = 1
  {% endif %}
2 Likes