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

lookml
filters

(Bryan Weber) #1

Included in this analytic block are two examples of using a date filter to dynamically create a reporting period range. This allows users to have two periods (selected period and previous period) and compare them. The first example compares any range of dates on the filter (‘in the past X days/months/etc’, ‘in the year’ or ‘is in range’) and the range that happened before it (equal by number of days). The second example expands the first example and allows the user to select other date filters (‘on or after’ or ‘is before’)

Note:
This is an expansion of use cases for date_start and date_end as a templated filter from this post. Please see the Usage Notes from the linked post above on the limitations of date_start and date_end.

Simple Example:

Compare any specified date range to the previous range of the same number of days. Using the combination of a filter field and a dimension to track which period the date belongs in. In the dimension, we compare the data’s timestamp to the filters start and end values to create the dynamic date range.

To compare the total sales from the last 30 days versus the previous 30 days, I’ve included the previous_period_filter and previous_period fields in my view file allows me to dynamically create the reporting period between the the most recent 30 days and the previous 30 days.

Here is the example Explore results:

 filter: previous_period_filter {
   type: date
   description: "Use this filter for period analysis"
   sql: ${previous_period} IS NOT NULL ;;
 }

 # For Amazon Redshift
 # ${created_raw} is the timestamp dimension we are building our reporting period off of
 dimension: previous_period {
  type: string
  description: "The reporting period as selected by the Previous Period Filter"
  sql:
  CASE
    WHEN {% date_start previous_period_filter %} is not null AND {% date_end previous_period_filter %} is not null /* date ranges or in the past x days */
      THEN
        CASE
          WHEN ${created_raw} >=  {% date_start previous_period_filter %}
            AND ${created_raw} <= {% date_end previous_period_filter %}
            THEN 'This Period'
          WHEN ${created_raw} >= DATEADD(day,-1*DATEDIFF(day,{% date_start previous_period_filter %}, {% date_end previous_period_filter %} ) + 1, DATEADD(day,-1,{% date_start previous_period_filter %} ) )
            AND ${created_raw} <= DATEADD(day,-1,{% date_start previous_period_filter %} )
            THEN 'Previous Period'
        END
      END ;; 
}
Advanced Example:

Expanding on the simple example, if we wanted the user to select other ranges (‘on or after’ or ‘is before’) and get results that correspond to the the period selected compared to the rest of the population, we can expand the LookML sql parameter for previous_period to include those conditions.

To compare the average sale price after May 1st, 2017, versus anytime before we can change our Previous Period Filter to on or after ‘2017-05-01’.

Here is the example Explore results:

# For Amazon Redshift
 # ${created_raw} is the timestamp dimension we are building our reporting period off of
dimension: previous_period {
      type: string
      description: "The reporting period as selected by the Previous Period Filter"
      sql:
        CASE
          WHEN {% date_start previous_period_filter %} is not null AND {% date_end previous_period_filter %} is not null /* date ranges or in the past x days */
            THEN
              CASE
                WHEN ${created_raw} >=  {% date_start previous_period_filter %}
                  AND ${created_raw} <= {% date_end previous_period_filter %}
                  THEN 'This Period'
                WHEN ${created_raw} >= DATEADD(day,-1*DATEDIFF(day,{% date_start previous_period_filter %}, {% date_end previous_period_filter %} ) + 1, DATEADD(day,-1,{% date_start previous_period_filter %} ) )
                  AND ${created_raw} < DATEADD(day,-1,{% date_start previous_period_filter %} ) + 1
                  THEN 'Previous Period'
              END
          WHEN {% date_start previous_period_filter %} is null AND {% date_end previous_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 previous_period_filter %} is null AND {% date_end previous_period_filter %} is not null /* on or before */
            THEN
              CASE
                WHEN  ${created_raw} <=  {% date_end previous_period_filter %} THEN 'In Period'
                WHEN  ${created_raw} >   {% date_end previous_period_filter %} THEN 'Not In Period'
              END
         WHEN {% date_start previous_period_filter %} is not null AND {% date_end previous_period_filter %} is null /* on or after */
           THEN
             CASE
               WHEN  ${created_raw} >= {% date_start previous_period_filter %} THEN 'In Period'
               WHEN  ${created_raw} < {% date_start previous_period_filter %} THEN 'Not In Period'
            END
        END ;;
    }

(Shawn Schwegman) #2

This is really helpful. I have a few questions on this:

  1. When I change the Previous Period Filter to “is in the past” and select “1 day”, nothing returns. How would you modify the previous period filter to look at today vs yesterday?
  2. What if you wanted to look at yesterday compared to the same day last week?
  3. What if you wanted to look at this week so far, compared to the same days last week? Example, if it’s Thursday, then I’d want to compare this Sunday - Thursday with the same days last week.

Again, I think this is really helpful, but there are many reasons for comparing the first 3 days of this week to the same period last week, comparing yesterday to the same day last week, or even comparing this month to the same days last of the previous month (e.g., the 1st - the 10th of this month vs last month).

I’m hoping there are a few modifications to this block that could also handle the above use cases? Thoughts?


(jonathon) #3

Hi @djshawn, Thanks for pointing this out to us. I spoke with @Bryan_Weber, and it looks like we may need to update some of the block (and possibly the Explore). We will post back after the update.

Thanks again for pointing this out.


#4

Hey @djshawn,

Thanks for bringing this to our attention! In regards to #1, I have updated the LookML above to solve the issue.

For #2 & #3, I recommend you check out this additional Discourse article. It provides solutions to these more complex comparison cases.

Thanks,
Rachel


#5

How we can calculate Month over Month analysis,
Its like month start to till date for present month and month start to till date for previous month,

Do anyone have any inputs on this?

Thanks,
Harika.


(Aleksandrs Vedernikovs) #6

Hi @Harika,

There is a good article written by my colleague here on how to create Month to Date and Year to Date Analysis!

Best,

Sasha


(Eric Hutcheson) #7

Here is the Simple Example syntax for Google BigQuery:

 filter: previous_period_filter {
    type: date
    description: "Use this filter for period analysis"
  }
  
dimension: previous_period {
  type: string
  description: "The reporting period as selected by the Previous Period Filter"
  sql:
  CASE
    WHEN {% date_start previous_period_filter %} is not null AND {% date_end previous_period_filter %} is not null /* date ranges or in the past x days */
      THEN
        CASE
          WHEN ${Transaction_raw} >=  {% date_start previous_period_filter %}
            AND ${Transaction_raw} <= {% date_end previous_period_filter %}
            THEN 'This Period'
          WHEN ${Transaction_raw} >= 
          TIMESTAMP_ADD(TIMESTAMP_ADD({% date_start previous_period_filter %}, INTERVAL -1 DAY ), INTERVAL
            -1*DATE_DIFF(DATE({% date_end previous_period_filter %}), DATE({% date_start previous_period_filter %}), DAY) + 1 DAY)
            AND ${Transaction_raw} <= 
            TIMESTAMP_ADD({% date_start previous_period_filter %}, INTERVAL -1 DAY )
            THEN 'Previous Period'
        END
      END ;; 
      }

(Eric Hutcheson) #8

Once you have this dynamic previous period block setup, you may want to visualize your current and previous periods side-by-side on a time series graph like the below example.

In order to do this, we need to derive a time dimension that applies to both periods. A quick and easy way to do this is to extract the number of days or weeks that have taken place since the start of each respective period (“Current” and “Previous”) using some of the same SQL logic that was used in the initial block setup. This can then be used as your time dimension on the X-axis.

GOOGLE BIGQUERY EXAMPLE

        dimension: start_of_period {
        hidden: yes
# this calculates the start date of each period
        type: date
        sql: CASE WHEN ${previous_period} = 'This Period'
                 THEN {% date_start previous_period_filter %}
                  WHEN  ${previous_period} = 'Previous Period'
                  THEN TIMESTAMP_ADD(TIMESTAMP_ADD({% date_start previous_period_filter %}, INTERVAL -1 DAY ), INTERVAL
                        -1*DATE_DIFF(DATE({% date_end previous_period_filter %}), DATE({% date_start previous_period_filter %}), DAY) + 1 DAY)
                        ELSE NULL END;;
                  }

          dimension: days_since_start_of_period {
          # use this in your x-axis for date-level time series (replace Transaction Date with your date)
            type: number
            sql: date_diff(${Transaction_date}, ${start_of_period}, DAY) ;;
          }

          dimension: weeks_since_start_of_period {
           # use this in your x-axis for week-level time series (replace Transaction Date with your date)
            type: number
            sql: round(${days_since_end_of_period}/7) + 1 ;;

AMAZON REDSHIFT EXAMPLE

dimension: start_of_period {
type: date
sql: CASE WHEN ${previous_period} = 'This Period'
       THEN {% date_start previous_period_filter %}
       WHEN  ${previous_period} = 'Previous Period'
       THEN DATEADD(day,-1*DATEDIFF(day,{% date_start previous_period_filter %}, {% date_end previous_period_filter %} ) + 1, DATEADD(day,-1,{% date_start previous_period_filter %} ) )
                              AND ${created_raw} < DATEADD(day,-1,{% date_start previous_period_filter %} ) + 1;;

(Sean Higgins) #9

This pattern is super flexible and can incorporate a bunch other dynamic variables.

For example, below is a pattern for checking vs the previous Week/Month/Year based on user selection. It also injects a WHERE clause if the Period Over Period Dimension is selected to adjust the date filter to fit the required date ranges which is super helpful if you’re using a database that incorporates partitioning.
It also changes this WHERE clause and does a simple filter on the date if you’re not doing PoP analysis. This way you have a consolidated single filter for absolute dates and your PoP comparisons:

parameter: previous_period_comparison_granularity {
    description: "Select the comparison period. E.g. choosing Month will compare the selected range against the same dates 30 days ago. "
    type: unquoted
    
    allowed_value: {
      label: "Week"
      value: "7"
    }
    allowed_value: {
      label: "Month"
      value: "30"
    }
    allowed_value: {
      label: "Year"
      value: "365"
    }
  }
  
  filter: previous_period_filter {
    label: "Previous Period/This Period filter Range"
    description: "Previous Period Filter for specific measures. User Date filter for any regular measures."
    type: date
    sql:
    {% if period_over_period._in_query %}
    (${created_date} >=  {% date_start previous_period_filter %}
    AND ${created_date} <= {% date_end previous_period_filter %})
     OR
     (${created_date} >= DATEADD(day,-{{ previous_period_comparison_granularity._parameter_value }}, {% date_start previous_period_filter %} )
     AND ${created_date} <= DATEADD(day,-{{ previous_period_comparison_granularity._parameter_value }}+DATEDIFF(day,{% date_start previous_period_filter %}, {% date_end previous_period_filter %}),{% date_start previous_period_filter %} ))
    {% else %}
    {% condition previous_period_filter %} CAST(${created_raw} as DATE) {% endcondition %}
    {% endif %}
    ;;
    }
    
    dimension: period_over_period {
      type: string
      description: "The reporting period as selected by the Previous Period Filter"
      sql:
      CASE
        WHEN {% date_start previous_period_filter %} is not null AND {% date_end previous_period_filter %} is not null /* date ranges or in the past x days */
          THEN
            CASE
              WHEN ${created_date} >=  {% date_start previous_period_filter %}
                AND ${created_date} <= {% date_end previous_period_filter %}
                THEN 'This Period'

                WHEN ${created_date} >= DATEADD(day,-{{ previous_period_comparison_granularity._parameter_value }}, {% date_start previous_period_filter %} )
                AND ${created_date} <= DATEADD(day,-{{ previous_period_comparison_granularity._parameter_value }}+DATEDIFF(day,{% date_start previous_period_filter %}, {% date_end previous_period_filter %}),{% date_start previous_period_filter %} )
              
                THEN 'Previous Period'
            END
            ELSE
            'This Period'
          END ;; 
    }


(Peggy Beard) #10

The syntax for this in Postgres is a bit tricky. It turns out that when we subtract timestamps in Postgres, we end up with an INTERVAL type, so we don’t need to add quotes or any Days syntax.

This is what works:

 sql: CASE
        WHEN {% date_start previous_period_filter %} is not null
            AND {% date_end previous_period_filter %} is not null /* date ranges or in the past x days */
          THEN
            CASE
              WHEN ${start_raw} >=  {% date_start previous_period_filter %}
                AND ${start_raw} <= {% date_end previous_period_filter %}
                THEN 'This Period'
              WHEN ${start_raw} >=   ({% date_start previous_period_filter %}::timestamp - INTERVAL '1 day') -
                 ({% date_end previous_period_filter %}::timestamp - {% date_start previous_period_filter %}::timestamp)
                AND ${start_raw} <= {% date_start previous_period_filter %}::timestamp - INTERVAL '1 day'
               THEN 'Previous Period'
            END
          END ;;