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
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 of date_start and date_end.
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 ;;
}
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 ;;
}
This is really helpful. I have a few questions on this:
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?
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.
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
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.
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 ;;
}
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;;
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 ;;
}
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 ;;
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?
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!
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).
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*]
}
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*]
}
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!
Thanks Izzy! I’ll be sure to use those backticks in the future.
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.
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 ;;
}
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.
Here’s the syntax of the simple example that I worked out for Presto.
filter: previous_period_filter {
type: date
description: "Use this filter for period analysis"
sql: ${previous_period} IS NOT NULL ;;
}
# For PrestoDB
# ${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 cast(${created_raw} as DATE) >= {% date_start previous_period_filter %}
AND cast(${created_raw} as DATE) <= {% date_end previous_period_filter %}
THEN 'This Period'
WHEN cast(${created_raw} as DATE) >= DATE_ADD('day',-1*DATE_DIFF('day',{% date_start previous_period_filter %}, {% date_end previous_period_filter %} ) + 1, DATE_ADD('day',-1,{% date_start previous_period_filter %} ) )
AND cast(${created_raw} as DATE) <= DATE_ADD('day',-1,{% date_start previous_period_filter %} )
THEN 'Previous Period'
END
END ;;
}
Casting, as below, might not be necessary for your use case but it was for me.
cast(${created_raw} as DATE)
BIG shout out @ryan_bouquet and @milli_koch for helping me over the course of 3 hours to figure out dynamic period comparison and the Presto syntax. Thank you so much. I learned a lot!
Same parametrized pattern for anyone that needs it for Postgres (using INTERVAL instead of DATEADD)
### Period over Period Example: https://discourse.looker.com/t/analytic-block-dynamic-previous-period-analysis-using-date-start-date-end/5361/9
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} >= ({% date_start previous_period_filter %}::timestamp ) - (INTERVAL '{{ previous_period_comparison_granularity._parameter_value }} day')
AND
${created_date} <= ({% date_end previous_period_filter %}::timestamp - {% date_start previous_period_filter %}::timestamp )
+ ({% date_start previous_period_filter %}::timestamp ) - (INTERVAL '{{ previous_period_comparison_granularity._parameter_value }} day')
{% 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} >= ({% date_start previous_period_filter %}::timestamp - INTERVAL '{{ previous_period_comparison_granularity._parameter_value }} day')
AND ${created_date} <= ({% date_end previous_period_filter %}::timestamp - {% date_start previous_period_filter %}::timestamp )
+ ({% date_start previous_period_filter %}::timestamp ) - (INTERVAL '{{ previous_period_comparison_granularity._parameter_value }} day')
THEN 'Previous Period'
END
ELSE
'This Period'
END ;;
}
###```
What if i want to created limited option filters ? such as a dropdown list of
Last week
This month
Last Month
Last 3 months
Last 6 months
This Year
Last year
Can you please help me with this
Hi @rachel_johnson,
Could you please provide the link to the updated code of #1 ?
Kind regards,
Shefali
A post was merged into an existing topic: Encapsulating Table Names in Quotes in SQL
There is one issue with the code that I have noticed nobody has mentioned. When you are applying the allowed values to month and year you are not taking into consideration the month of February, that has only 28 days, or months with 31 days. In regards to year, you are not taking into consideration leap years, like 2020, where there are 29 days in February, and 366 days in the year.
How would you update the code to consider these variables?
That’s a great point, Alan. I think maybe the most correct approach would be to build out a dimension that contains the # of days in a given month and reference it there— This stackoverflow article has some ideas ranging from simple to crazy:
For leap years, I guess you could take a similar approach, or maybe just have a simple CASE statement to account for them. You could do the same for February, I suppose.
I am putting here another version which basically allows you to select the timeframe , and Looker will automatically get the previous period associated with this timeframe:
parameter: date_granularity {
type: unquoted
allowed_value: { value: "DAY" label: "Day"}
allowed_value: { value: "WEEK" label: "Week"}
allowed_value: { value: "MONTH" label: "Month"}
allowed_value: { value: "QUARTER" label: "Quarter"}
allowed_value: { value: "YEAR" label: "Year"}
default_value: "MONTH"
}
parameter: complete_period {
type: unquoted
allowed_value: { value: "Yes" }
allowed_value: { value: "No" }
default_value: "Yes"
}
dimension_group: current {
hidden: yes
type: time
sql:{% if complete_period._parameter_value == "Yes" %}
cast(date_add(current_date, INTERVAL -1 {% parameter date_granularity %} ) as timestamp)
{% elsif complete_period._parameter_value == "No" %}
cast(current_date as timestamp)
{% endif %} ;;
}
dimension: current_period {
hidden: yes
label_from_parameter: date_granularity
sql:
{% if date_granularity._parameter_value == "DAY" %}
${current_date}
{% elsif date_granularity._parameter_value == "WEEK" %}
${current_week}
{% elsif date_granularity._parameter_value == "MONTH" %}
${current_month}
{% elsif date_granularity._parameter_value == "QUARTER" %}
${current_quarter}
{% elsif date_granularity._parameter_value == "YEAR" %}
${current_year}
{% endif %} ;;
}
dimension_group: previous {
hidden: yes
type: time
sql: {% if complete_period._parameter_value == "Yes" %}
cast(date_add(current_date, INTERVAL -2 {% parameter date_granularity %} ) as timestamp)
{% elsif complete_period._parameter_value == "No" %}
cast(date_add(current_date, INTERVAL -1 {% parameter date_granularity %} ) as timestamp)
{% endif %}
;;
}
dimension: previous_period {
hidden: yes
label_from_parameter: date_granularity
sql:
{% if date_granularity._parameter_value == "DAY" %}
${previous_date}
{% elsif date_granularity.period._parameter_value == "WEEK" %}
${previous_week}
{% elsif date_granularity.period._parameter_value == "MONTH" %}
${previous_month}
{% elsif date_granularity.period._parameter_value == "QUARTER" %}
${previous_quarter}
{% elsif date_granularity._parameter_value == "YEAR" %}
${previous_year}
{% endif %} ;;
}
filter: only_finished_periods {
description: "Filter out the current time period (week, month, etc..) which has not ended."
type: yesno
sql:
${purchase_raw} < timestamp_trunc(current_timestamp,{% parameter date_granularity %})
;;
}
dimension: period_comparison {
type: string
description: "The reporting period as selected by the Period Filter"
sql:
case WHEN ${purchase_period} is not null then
CASE
WHEN ${purchase_period}= ${current_period}
THEN 'This {% parameter date_granularity %}'
WHEN ${purchase_period} = ${previous_period}
THEN 'Previous {% parameter date_granularity %}'
end
END;;
}
also to remove nulls, you need to put this in your explore:
sql_always_where: {% if ios_transactions.date_granularity._in_query %}
${period_comparison} is not null
{% else %}
1=1
{% endif %};;
Hi @Cyril_MTL_Analy 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
Good Catch @jcarter1 and thanks for the kind words!
I have corrected the Gist with your comment 👍