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

filters
lookml
open
normal_priority
reply
(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 ;;
    }
0 Likes

Can we pass filter value in calculation. If yes, how?
(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?

0 Likes

(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.

0 Likes

#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

0 Likes

#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.

0 Likes

(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

0 Likes

(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 ;; 
      }
1 Like

(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;;
0 Likes

(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 ;; 
    }

2 Likes

(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 ;;
1 Like

(Ben Silverstein) #11

Thanks for this pattern! I’ve taken this another direction, and have created standard measures pre-filtered on ‘Previous Period’ and ‘This Period’. Unfortunately, things are feeling a bit slow. Any ideas what I could check to see where things have gone wrong?

0 Likes

(Izzy) #12

What in particular is slow? Have you narrowed it down to just those 2 measures, and, particularly, is it noticeably slower than the example before you took it in your own direction?

Since the meat of this block is wrapped up in pretty bulky SQL case statements, I’m not surprised to hear that modifying it might lead to some performance concerns— posting your example measures would help us see if there’s some clear ‘gotchas’ in the sql!

0 Likes

(Ben Silverstein) #13

Hey Izzy, thanks for your response! Based on this pattern, I’ve tried a couple of different things, but behind the scenes I think they’re more or less equivalent. While the original pattern runs very quickly (~2-4 seconds), neither of these does (~30-50 seconds for a quarter’s worth of comparative data).

  1. Based on the SQL code in the design pattern, I’ve linked two measures to filter on the date range set by the period_filter parameter value.
  filter: period_filter {
    label: "Comparison Period Filter"
    description: "Filter for any Comparison Period fields"
    type: date
    sql:
    {% if period_over_period._in_query %}
    (${posting_date} >=  {% date_start period_filter %}
    AND ${posting_date} <= {% date_end period_filter %})
     OR
     (${posting_date} >= DATEADD(day,-{{ comparison_period_picker._parameter_value }}, {% date_start period_filter %} )
     AND ${posting_date} <= DATEADD(day,-{{ comparison_period_picker._parameter_value }}+DATEDIFF(day,{% date_start period_filter %}, {% date_end period_filter %}),{% date_start period_filter %} ))
    {% endif %}
    ;;
  }



 measure: sales_last_period_trial {
    type: sum
    sql:
    CASE
      WHEN {% date_start period_filter %} IS NOT NULL AND {% date_end period_filter %} IS NOT null /* date ranges or in the past x days */
      THEN
        CASE
          WHEN ${posting_date} >= DATEADD(day,-{{ comparison_period_picker._parameter_value }}, {% date_start period_filter %} )
          AND ${posting_date} <= DATEADD(day,-{{ comparison_period_picker._parameter_value }}+  DATEDIFF(day,{% date_start period_filter %}, {% date_end period_filter %}),  {% date_start period_filter %} )
          THEN ${sales}
        END
        END  ;;
    drill_fields: [item_details*]
}

measure: sales_this_period_trial {
    type: sum
    sql:
    CASE
      WHEN {% date_start period_filter %} IS NOT NULL AND {% date_end period_filter %} IS NOT null /* date ranges or in the past x days */
      THEN
        CASE
          WHEN ${posting_date} >=  {% date_start period_filter %}
          AND ${posting_date} <= {% date_end period_filter %}
          THEN ${sales}
        END
        ELSE ${sales}
    END ;;
    drill_fields: [item_details*]
  }
  1. Simply filtering on period_over_period as appropriate to flag records as ‘Previous Period’ or ‘This Period’
  dimension: period_over_period {
    type: string
    description: "The reporting period as selected by the Previous Period Filter"
    sql:
      CASE
        WHEN {% date_start period_filter %} IS NOT NULL AND {% date_end period_filter %} IS NOT null /* date ranges or in the past x days */
        THEN
          CASE
            WHEN ${posting_date} >=  {% date_start period_filter %}
            AND ${posting_date} <= {% date_end period_filter %}
            THEN 'This Period'
            WHEN ${posting_date} >= DATEADD(day,-{{ comparison_period_picker._parameter_value }}, {% date_start period_filter %} )
            AND ${posting_date} <= DATEADD(day,-{{ comparison_period_picker._parameter_value }}+DATEDIFF(day,{% date_start period_filter %}, {% date_end period_filter %}),{% date_start period_filter %} )
            THEN 'Previous Period'
          END
          ELSE
            'This Period'
            END ;;
  }

  measure: sales_last_period {
    type: sum
    description: "Sales ($) Last Period with P/P Selector"
    sql: ${sales} ;;
    filters: {
      field: period_over_period
      value: "Previous Period"
    }
    value_format_name: usd_0
    drill_fields: [item_details*]
  }

  measure: sales_this_period {
    type: sum
    description: "Sales ($) This Period with P/P Selector"
    sql: ${sales} ;;
    filters: {
      field: period_over_period
      value: "This Period"
    }
    value_format_name: usd_0
    drill_fields: [item_details*]
  }
0 Likes

(Izzy) #14

I quickly edited those code blocks to make em more readable for others (adding 3 backticks (`) turns the block beneath them into code), and I’ll take a look later on!

1 Like

(Ben Silverstein) #15

Thanks Izzy! I’ll be sure to use those backticks in the future.

0 Likes

(Izzy) #16

Nothing leaps out at me about those examples, other than the fact that adding that much case when logic to the measures (filters pretty much just write case whens, so using the filters: parameter is actually adding yet another layer) could be slowing things down— This could maybe be exacerbated if you have Symmetric Aggregates kicking in due to a one-to-many join. Does the generated SQL from that query look really complicated?

One built-in troubleshooting tool is the “Explain in SQL Runner” option you get when looking at the SQL tab of an explore, which shows you the query plan and can highlight specific operations that are causing a slowdown.

0 Likes

(Max Reid) #17

For the case when a user chooses a particular day, I added another case statement to the previous_period dimension, see below

dimension: previous_period {
    type: string
    description: "The reporting period as selected by the Previous Period Filter"
    sql:
      CASE
        WHEN DATEDIFF(day,{% date_start previous_period_filter %},{% date_end previous_period_filter %}) = 1
       /* when range is on one day */
        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,{% date_start previous_period_filter %})
                AND ${created_raw} <= DATEADD(day,-1,{% date_end previous_period_filter %})
            THEN 'Previous Period'
            END)
            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 ;; 
  }
0 Likes

(Ben Silverstein) #18

This is an amazing pattern for Rolling, but does anyone know of a pattern that would allow me to do this same but for Calendar Periods (Month, Quarter, Year, Day)?

I.e. if my business user selects Month, I’d want This Period’s filter to include all values within this month, but with the current Rolling logic above, all I could do is to subtract a fixed number of days from the date_start and date_end values, which gets messy since months have different numbers of days. Hope I explained this alright.

0 Likes