Question

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


Userlevel 4

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

28 replies

Userlevel 5
Badge

Hello there!


We just published the final part of our own version of the PoP block:



Hi @Cyril_MTL_Analytics great article and explanation.

Finally, period-over-period all in one place! It’s been pretty hard following some of the threads started a 1-2 years ago with continued updates til now so to see it concisely put is great.


One note - most of your article is great and everything worked the first time but I noticed what “may” have been a find/replace typo.


In the last article, part 3, you have the dynamic liquid labels dynamic_labels_in_liquid_with_quarter_redshift. I was getting an error message and tracked it down to what I think is select_timeframe_quarter should actually be select_timeframe

Userlevel 5
Badge

Good Catch @jcarter and thanks for the kind words!

I have corrected the Gist with your comment 👍

Reply