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 fordate_start
anddate_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 ofdate_start
anddate_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 ;;
}
© 2020 GitHub, Inc.