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.
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.
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:
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
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
Any granularity - Choose how granular you want your results to be by picking the appropriate date dimension from the dimension group
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.
The process for using this in your explore is as follows:
Date Range
to choose your initial date rangeCompare To (Templated)
to choose a templated comparison range OR Add the filter Compare To (Custom)
to choose a custom comparison rangeComparison Periods
filterCurrent Period Date
, don’t use any date dimension from any other viewPeriod
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:
_date_comparison.view.lkml
into your project_date_comparison
view by adding the parameter extends: [_date_comparison]
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 correctlysql_always_where
clause defined in the model file here. Replace all instances of <your_view_name>
with your view name.Hi Izzy, is there any update on this?
This date comparison block is really good, however we have found one issue that occurs and I was wondering if anyone here had a workaround or a fix for it.
When comparing a measure, e.g number of users, where there are no events for ‘this period’ but some events for ‘last period’ it will only show ‘last period’ in the period dimension. This becomes an issue when using single value visualisations as it will misleadingly show the last period value instead of this period’s value of 0.
Thanks! 🙂
@bencannon this is a really cool block.
I’m curious if you’ve run into this issue with overlapping comparisons. Consider the following:
The “This period” value is correct. The “Last period” value is wrong, but Last period + This period = What we would expect Last period to be.
Similarly, if we flip the comparisons like this:
We get these results:
– This period: $100 (correct)
– Last period: $0 (wrong)
We implemented this using the SQL Server syntax found above in the comments. I’m curious if the same holds true for BigQuery. Any ideas??
Thanks!
Jesse, the challenge seems to be that it’s just a simple CASE to determine whether a date falls under “This Period” or “Last Period” (etc.) to decide where it should be aggregated, but a CASE can’t put it in both buckets (This and Last Period). I’ve disabled the Custom range for the time being in my instance because it’s a potentially wrong avenue to send people down.
I think a potential solution would be rather than having one dimension called ‘period’, to actually have a yesno per period (in_period1/2/3/4), and to aggregate each period based on in_periodX == ‘yes’. The downside is you wouldn’t be able to pivot on ‘this period/last period’, but you could quickly create the aggregates as you need them.
this makes sense, because the comparison periods are over lapping, and the code currently only buckets the values in one or the other. As @bens1 points out, its a CASE statement driving this. Because of the flexibility of the of the custom filter, it is possible for the user to miss use it.
The base use case for this block is ability to compare dates on one X Axis. I’d need to think about how the code could change to accommodate overlapping comparisons without impacting this.
If you have any ideas or manage to develop some code to change this, feel free to submit to the GIT repo and I can review and incorporate it 🙂
@bens1 and @ - I feel a bit silly for not catching that. Makes perfect sense.
A very simple pattern that my colleague @Kevin_McCarthy showed me goes something like this:
view: arbitrary_date_comparison {
derived_table: {
sql: select 'period_1' as period union all select 'period_2' ;;
}
dimension: period {
description: "Pivot me!"
}
filter: date_selector_1{
type: date
}
filter: date_selector_2 {
type: date
}
}
sql_where
clause:explore: customers {
join: arbitrary_date_comparison {
relationship: one_to_one
type: cross
sql_where:
(${arbitrary_date_comparison.period} = 'period_1'
and
{% condition arbitrary_date_comparison.date_selector_1 %}${customers.created_raw}{%endcondition%})
or
(${arbitrary_date_comparison.period} = 'period_2'
and
{% condition arbitrary_date_comparison.date_selector_2 %}${customers.created_raw}{%endcondition%})
;;
}
}
The cross join allows you to take your query and union it with itself. The sql_where
then allows you to filter each copy of the query independently. The end product:
Once again, all credit goes to @Kevin_McCarthy. Special shoutout to @fabio1 who has pioneered a lot of this stuff!
I was able to get this converted over to snowflake. Essentially you need:
** As soon as I have validated my changes I will share work here.
Hi @, was wondering if you could share exactly which dimensions/filters you added convert_tz: no
to. I was able to convert all of the code over to Snowflake but the timezone conversion was leading to incorrect data. Were you able to get the expected result even with the user timezone conversion enabled?
Just wanted to offer a hug (or the option to not be hugged, if that’s preferable) to the first person who can come up with a solution not requiring the sql_always_where or sql_where clause. Date comparisons are used for so much analysis that it feels like half my model code is related to inserting this clause where needed.
Hi all
This code block has been updated to allow timezone conversion to take place at query time (which is set in your connection settings).
Please see the git repo to update your code
Thanks!
Jamie
@b_marshburn Were you able to validate this running in Snowflake? If so, do you mind sharing your work? Thanks!
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!
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.
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.
order stated Covid-19 pendamic united states of america and asia Covid-19 data stop international pendamic national security agency challenging Cloud looket under world finely real jobs and real challenging small busniess alls capitol united stetes of america international dynamic natinoanl security e.g. washing change knaw
role of missile and role of anti blustic fair fight wasing change knaw statedmant jesus jeffrey, minister of deprance under justice off duty fair order stated
http://hack<org/worldpress// next target UMNO red party fuck you tonight target hack 100, trilian open challenge i fuck your departmant malaysia powerfull politic fuck your justice malaysia
Real fight hackaday fuck your policy malaysia
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
International political agenda Social media flowing the agendar link dabal click full abedances, Systems justifiction role of low robot , and robot have to posiboly tracking alls systems mejority real state 30 ton personel car tracking order social media power by fighting agency make sure the jeffrey peoporty real state suspect departmant malaysia and suspect employed employess pepole malaysian order exchange warker malaysian pepole, more flowing the jeffrey facebook page,https://facebook.com/Jeffrey
Justifiction his time is not mater fredal agency wheter f**k tallk to fredal agency like this broad dont tallk to busniess malaysia i f**k his criminal country malaysia and more f**k malaysian alls agency i have to my power open challenge for roler negara ketua besar you like this fight come face to face i show you my power wheter f**k you minedad him you dont tallk to about his islamic country and islamic low i fuck your islamic since your says him change power just for him i have to for ever and ever and ever attack his criminal country malaysia fuck you state social media coming soon flowing the jeffrey next website https://www.instagram.com/muhdjefry3
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
I have a join in my model file like this
Where should I paste the sql_always_where query in the model file?
My ‘your_view_name’ is date_dim