Year-over-year comparison based on the day of the week.

Yoni318
Participant I

I have another question: Is it possible to make a year-over-year comparison based on the day of the week.

For example, first Monday of January 2022 to the first Monday of January 2021?

0 5 977
5 REPLIES 5

Dawid
Participant V

Yes you could do something like that.

  1. Add week_of_year and day_of_week timeframes to your field
  2. Filter your exploration by Week of Year = 1 and Day of Week = Monday
  3. Select Year as dimension
  4. Select xyz as measure

Depending on your dialect and database configuration, you may see that using different days of week may not work, for example if Thursday belongs to the prior week then technically first Friday in New Year might still be week 53 not 1. 

This is a common occurrence but that’s how weeks are implemented using ISO standard. If it wiill break things for you, you may have to do some transformations in your data model.

https://community.looker.com/lookml-5/how-do-have-wk-52-show-as-2021-and-not-2022-28847

Yoni318
Participant I

In our data structure, the week of the year is Monday's date i.e for 2022 it equals 2022-01-03.

Can I perform the same logic here?

Dawid
Participant V

What logic are you referring to? Add those timeframes in your LookML and see what the data looks like, that should give you some direction. I on the other hand can’t preview it with your data

Yoni318
Participant I

I meant the logic you presented in your answer. 

When I choose the day of the week = Monday for example I'm getting each Monday's date. 
But the week of the year is actually a date format. Since I can give a number such as the first/second…  day of the week I think it won't work. Feel free to what I've pulled so far. 

9a9c97f8-19e3-4de8-b9c8-29c3a532e996.png

Have you tried the ‘week_of_year’ timeframe that you can use with dimension groups of type: time ?

This would allow you to set a day of week filter and see ‘week of year’ returned as an integer, so you could see ‘week 1’ from both years.

Docs on the dimension group and timeframes are below in case you’re not familiar:

https://docs.looker.com/reference/field-params/dimension_group#timeframes

Top Labels in this Space
Top Solution Authors