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

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?

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.

Userlevel 2

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.

Userlevel 2

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

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;;
Userlevel 4

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

Userlevel 1

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 ;;
Userlevel 5
Badge

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?

Userlevel 7
Badge +1

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!

Userlevel 5
Badge

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*]
}
Userlevel 7
Badge +1

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!

Userlevel 5
Badge

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

Userlevel 7
Badge +1

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 ;;
}
Userlevel 5
Badge

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!

Userlevel 4

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

Userlevel 7
Badge +1

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?

Userlevel 7
Badge +1

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

Reply