[Analytic Block] Dynamic Previous Period Analysis using date_start, date_end

[Bryan_Weber]

Looker has created this block to make analyzing data easier and more efficient. This Data Block is made available on an “as is” basis, meaning there will not be updates moving forward.

This analytic block includes two examples of using a date filter to dynamically create a reporting period range, which allows users to compare two periods: the selected period and its previous period.

The Basic Example compares any range of dates with the filter conditions: in the past X days/weeks/months, in the year, or is in range, and will also output in the same defined time range that happened before (e.g. total sales from the last 30 days vs. the previous 30 days).

The Advanced Example expands on the first example and allows the user to select less absolute date filter conditions, such as on or after or is before.

This is an expansion of use cases for date_start and date_end in a templated filter from the Help Center article Using date_start and date_end with Date Filters. See the Usage Notes from the linked article regarding the limitations of date_start and date_end.

Basic Example

With this method, you can compare any absolute date range to the previous range of the same amount of time. For example, you can compare the total sales from the last 30 days versus the 30 days before that. To do this, you can create a filter field to accept user input for defining the current period and a dimension to output whether a date belongs in this period or in the previous period, for period comparison. In the dimension, you can compare the data’s timestamp to the filter field’s start and end values with the templated filter syntax, {% date_start date_filter_name %} and {% date_end date_filter_name %}, to create the dynamic date range.

Below is the code for the this_period_filter filter field and period dimension that you can add to your view file, replacing the references to ${created_raw} with the raw timeframe from your dimension_group or date field. This combination of filter, dimension, and templated filter allows you to dynamically create the reporting period between the the most recent period of X amount of time and the previous period of the same X amount of time.

The following examples are in the SQL dialect for Redshift. Please be sure to update the syntax as appropriate for your SQL dialect.

Here is the LookML for the basic example:

# For Amazon Redshift
filter: this_period_filter {
   type: date
   description: "Use this filter to define the current and previous period for analysis"
   sql: ${period} IS NOT NULL ;;
 }
# ${created_raw} is the timestamp dimension we are building our reporting period off of

dimension: period {
  type: string
  description: "The reporting period as selected by the This Period Filter"
  sql:
  CASE
    WHEN {% date_start this_period_filter %} is not null AND {% date_end this_period_filter %} is not null /* date ranges or in the past x days */
      THEN
        CASE
          WHEN ${created_raw} >= {% date_start this_period_filter %}
            AND ${created_raw} <= {% date_end this_period_filter %}
            THEN 'This Period'
          WHEN ${created_raw} >= DATEADD(day,-1*DATEDIFF(day,{% date_start this_period_filter %}, {% date_end this_period_filter %} ) + 1, DATEADD(day,-1,{% date_start this_period_filter %} ) )
            AND ${created_raw} <= DATEADD(day,-1,{% date_start this_period_filter %} ) + 1 
            THEN 'Previous Period'
        END
    END ;; 
}

Advanced Example

Expanding on the Basic Example, if you want the user to be able to select less absolute filter conditions to define the current period, such as on or after or is before, and see results that correspond to the the period selected (compared to the rest of the population) you can expand the LookML sql parameter in the period dimension to include those conditions. For example, a user may want to compare the average sale price after May 1st, 2019, versus any time before. With the following solution, the user will be able to update the this_period_filter to on or after '2019-05-01' in an Explore.

As with the above example, make sure to replace the references to ${created_raw} with the raw timeframe from your dimension_group or date field.

Below is the LookML for the Advanced Example:

# For Amazon Redshift

filter: this_period_filter {
   type: date
   description: "Use this filter to define the current and previous period for analysis"
   sql: ${period} IS NOT NULL ;;
 }
# ${created_raw} is the timestamp dimension we are building our reporting period off of

dimension: period {
  type: string
  description: "The reporting period as selected by the This Period Filter" 
  sql:
    CASE
      WHEN {% date_start this_period_filter %} is not null AND {% date_end this_period_filter %} is not null /* date ranges or in the past x days */
        THEN
          CASE
            WHEN ${created_raw} >= {% date_start this_period_filter %}
              AND ${created_raw} <= {% date_end this_period_filter %}
              THEN 'This Period'
            WHEN ${created_raw} >= DATEADD(day,-1*DATEDIFF(day,{% date_start this_period_filter %}, {% date_end this_period_filter %} ) + 1, DATEADD(day,-1,{% date_start this_period_filter %} ) )
              AND ${created_raw} < DATEADD(day,-1,{% date_start this_period_filter %} ) + 1
              THEN 'Previous Period'
          END
        WHEN {% date_start this_period_filter %} is null AND {% date_end this_period_filter %} is null /* has any value or is not null */
          THEN CASE WHEN ${created_raw} is not null THEN 'Has Value' ELSE 'Is Null' END
        WHEN {% date_start this_period_filter %} is null AND {% date_end this_period_filter %} is not null /* on or before */
          THEN
            CASE
              WHEN ${created_raw} <= {% date_end this_period_filter %} THEN 'In Period'
              WHEN ${created_raw} > {% date_end this_period_filter %} THEN 'Not In Period'
            END
          WHEN {% date_start this_period_filter %} is not null AND {% date_end this_period_filter %} is null /* on or after */
            THEN
              CASE
                WHEN ${created_raw} >= {% date_start this_period_filter %} THEN 'In Period'
                WHEN ${created_raw} < {% date_start this_period_filter %} THEN 'Not In Period'
              END
            END ;;
 }