LinkedIn
Twitter
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:
-
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.
How to use
The process for using this in your explore is as follows:
- Add the filter
Date Rangeto choose your initial date range - Add the filter
Compare To (Templated)to choose a templated comparison range OR Add the filterCompare To (Custom)to choose a custom comparison range - If you have chosen a templated range, you may choose to add more periods by using the
Comparison Periodsfilter - Choose your date dimension - only pick from the dimension group
Current Period Date, don’t use any date dimension from any other view - Choose your other dimensions and measures as usual
- Finally, pivot on
Period - 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:
- Copy the view file
_date_comparison.view.lkmlinto your project - In the view file where the date dimension you would like to be able to compare is, extend the
_date_comparisonview by adding the parameterextends: [_date_comparison] - In the same view file, add two new dimensions,
event_dateandevent_raw.These are simply<your_date_dimension>_dateand<your_date_dimension>_rawrespectively. This step is just so that naming convention used in the_date_comparisonview works correctly - In the relevant explore LookML, add in the
sql_always_whereclause defined in the model file here. Replace all instances of<your_view_name>with your view name.
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:
- Construct a simple query that returns Revenue for last 14 days. The result is $100.
- Construct a simple query that returns Revenue for the last 7 days. The result is $75
- Now, let’s use this block to construct a query that compares last 7 days to last 14 days. Like this:
I now get this result:
– This period: $75
– Last period: $25
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!
@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:
- Build a derived table with two rows and one column. Add some empty date filters like so:
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
}
}
- Join it into your explore using a cross join and add the following
sql_whereclause:
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!
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
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
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
