Data comparaison with same day last year on lookerstudio

You might be able to pull it off with Looker Studio alone, but it may require a lot of tinkering with dates, and may not work in all cases. (YMMV)

One other option is to use Google Sheets as an intermediary. If you have access to Supermetrics for Google Sheets, then you can first load data into the spreadsheet and let us compare values for you while taking weekdays into account.

The only thing left would be to surface that data in Looker Studio with the Sheets connector.

HTH

Hi Paul,

The new time comparison chart (you are part of the closed beta I think), has an option to achieve the same.

This doesn’t give you the flexibility to use it in other charts, but is only a click away.

https://lookerstudio.google.com/u/0/reporting/880f7603-cd08-4c83-8fa0-800d0cc948b5/page/p_thqselp1hd

Hello all, 

Thanks for your answers ! Really helpful :) 

To let you David, for an unkown reason with some dates your formula is working and for some it’s not , here a e.g : (like 18 & 19 August isn’t OK but 17 to 13 ok and the 12th it’s not) I will try to investigate it but it’s not fully helping me

I will try to use community chart (I’m part of the closed beta but I’m using a source where I don’t have access to modify it yet) 

And Pavel, thanks for your answer, but since mostly all my queries are sending the data from SuperMetrics to GCP, I won’t want to change it and to host it into a spreadsheet, but it’s good to know ! 

Thanks for you support & answers :D 

Paul T

Hi Paul,

Another thing came in my mind. Marcus Stade and Patrick Mohr gave a workshop recently to show how they do YoY comparison with BigQuery. They only do this on week level, but maybe this can be modified to achieve a day-over-day comparison also...

https://share.descript.com/view/pgmk3S9cXZt

@David Magdolen, I found your awesome formula when searching the forum. Thanks!

I wonder if I may trouble you for an additional tip?

How would I go about using it? I mean, I basically want to setup a time series chart with e.g. Sessions the last 28 days, and have it show a second line with sessions last year.

that’s a tricky one, maybe @dataviz has a better solution than the one I am about lay out.

You could use a combination of calculated fields and a blend for a “self-join” inside Looker Studio as described here: https://lookerstudiobible.com/how-to-make-a-same-day-last-year-comparaison-in-looker-studio-739b3a61af81

In essence, you use a combination of Year, Week and Weekday as join key in the blend and for last year’s data you “add” a year so that the blend will “match” last year’s data with this year’s data for the same week and weekday. You also have to ensure that your default date range is large enough to include this year’s and last year’s data, otherwise the blend will not work.
So in that regard, with this solution you won’t be able to limit the date range to the last 28 days and have the blend find the matching weekdays from a year ago.

Sorry for the stuffy explanation.

@phillipvs 

Looker Studio has advanced date frame and comparison date range functions that you may be able to configure to your exact needs. Please be aware that Looker Studio has just updated them, and they are quite buggy at the moment.

Neverless, I was able to create a quick demo.

If you want to compare exact dates (November 1, 2024 compared to November 1, 2023), you can simply compare to last year, but in most cases, we want to compare day-over-day.

Another option would be to compare “minus 1/28” days to “minus 364/392 days”. The comparison would still be day-over-day, but the end date is always “yesterday”.

Hope this helps!

ralph