Question

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

  • 6 January 2022
  • 5 replies
  • 44 views

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?


5 replies

Userlevel 6
Badge +1

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.

 

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?

Userlevel 6
Badge +1

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

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. 

 

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

 

Reply