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

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 ;;
 }
2 28 4,949
28 REPLIES 28

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.

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

Harika1
Participant I

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.

Hi @Harika1,

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

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

bens1
Participant V

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!

bens1
Participant V

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*]
  }

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!

bens1
Participant V

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

bens1
Participant V

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

ajones
Participant I

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:

Get total no of days in given month in Google BigQuery?

google-bigquery

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

Hello there!

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

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 👍

Top Labels in this Space
Top Solution Authors