Date Comparison Block


Userlevel 2

Date Comparison Block


What is this block?


This block allows for comparisons from one date period to another, or from one date period to a number of periods. It provides extensive flexibility in comparing date ranges, and presenting these back as a single visualisation. The code can be found in this repo in the Datatonic GitHub.


Motivation


There are already a few blocks that allow for comparison between periods in Looker. The motivation for this block was to combine all the elements from the other blocks in a way that is intuitive, flexible and fast to use.


Solution


The approach was to use the intuitive style of date comparison that is used in Google Analytics - where the basic idea is that you choose the current date range that you’re interested in, then choose the comparison date range in the past. There are some templated period filters to make this easy for the user, but also custom filters to allow flexibility in comparing dates. The features are:




  1. Templated comparison periods - Once the initial period of interest is chosen, rather than manually choose the period before, there is a range of options presented such as “Previous Period”, “Previous Month”, “Previous Year” etc




  2. Custom comparison periods - in the situation where any of the above don’t fit what you are trying to see, then a custom previous date range may be chosen




  3. Any granularity - Choose how granular you want your results to be by picking the appropriate date dimension from the dimension group




  4. Multiple periods - Choose the number of periods you would like to compare, this is only available for templated periods, e.g January this year vs January last year vs January 2 years ago etc.




How to use


The process for using this in your explore is as follows:



  1. Add the filter Date Range to choose your initial date range

  2. Add the filter Compare To (Templated) to choose a templated comparison range OR Add the filter Compare To (Custom) to choose a custom comparison range

  3. If you have chosen a templated range, you may choose to add more periods by using the Comparison Periods filter

  4. Choose your date dimension - only pick from the dimension group Current Period Date, don’t use any date dimension from any other view

  5. Choose your other dimensions and measures as usual

  6. Finally, pivot on Period

  7. Hit run


How to implement


Syntax assumes a BigQuery connection, you may need to adjust this for other database connections. To have this available to use in your explores and dashboards there are a few steps:



  1. Copy the view file _date_comparison.view.lkml into your project

  2. In the view file where the date dimension you would like to be able to compare is, extend the _date_comparison view by adding the parameter extends: [_date_comparison]

  3. In the same view file, add two new dimensions, event_date and event_raw.These are simply <your_date_dimension>_date and <your_date_dimension>_raw respectively. This step is just so that naming convention used in the _date_comparison view works correctly

  4. In the relevant explore LookML, add in the sql_always_where clause defined in the model file here. Replace all instances of <your_view_name> with your view name.


59 replies

Userlevel 2

no just BQ, but we are going to be working with snowflake too now they are going to be hosting on Google cloud, so i will try that out in the future.

I have tried for the snowflake code. Details can be found here -

summerof69-looker-date-comparison

Hello everyone,


First, thank you for creating this very useful block and thanks to the community for providing a snowflake version!


We’re trying to make a month over month comparison (Sales from April compared to March and February for example) , but is it really possible since months don’t have the same number of days (30 days vs 31 days)?

We tried to use the “Compared To Previous Period” and “Compared To Previous Month” filters but the figures are not correct and we think it might be because of the number of days difference between each month. Or maybe we are doing something wrong?


Thank you for your help!

Hello everyone, thanks for all of the great information in this post!


I’m working on getting this implemented and have come across the following error:


“ERROR: column “this period” does not exist in transactions”


transactions is the table I’m querying in our Redshift database. We’ve tried adapting the code for Redshift. I’m wondering if there may be an issue there. However I wanted to post here just in case it might be something obvious I am not seeing. Thanks!

Userlevel 2

Its very important to note that “compare to previous month” is not actually comparing calendar months. It is comparing days. E.g. if the current month has 30 days, and you select “compare to previous month”, it will actually compare to the previous 30 days, not calendar month.


I’ve been meaning to figure a way to account for this later. But right now thats how it functions.

Userlevel 2

have you extended the view into the base view which the explore is using or a joined one?

Hey Team,
 

Firstly, this is an awesome block
 could somebody help me out how would we create measures for percentage_change and difference between the 2 pivoted period, rather then taking an approach to create table calculations for them as would it give more flexibility. For example, we would able to  custom value_format, based on the value.

I’m using this code block as is. I’m running prior period comparisons using ‘current_period_date’ and ‘current_period_week’. It works perfectly for current_period_date when I pivot on period and look at measure comparisons with prior weeks.

However for the same thing with current_period_week instead, none of the prior weeks get populated (all nulls) except for the farthest week (last when sorted descending) in the table. 

Has anyone run into this issue and/or have a fix for this? @bencannon 

Hi, i’m facing problems when i use “2b compare to custom”. Everytime i use in a range, it works as expected, the problem is when i compared one day against one day. For the original one, it works fine, but for the compared one, i get results for this day and the next one. Any idea how to solved it ? Thanks in advance

Reply