Comparing a Previous Week to that Same Week Last Year

Userlevel 5

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

It is common to compare things like the sales from a specific week of this year with the sales from the same week in previous years. Let's say you want to compare last week to the same week last year. There are two ways to accomplish this:

Weeks Starting on Monday

If you are using Looker's default weeks starting on Monday, you can do this easily by creating a matches (advanced) filter on a date field (in the example below we use a week timeframe to show a count by week) with the following filter expression:

1 week ago for 1 week, 53 weeks ago for 1 week

This filter will output something like this:

Note: We will need to turn off the dimension fill feature on the date field in the Explore to compare only the time periods that were filtered for. Otherwise, we may see NULLs in the result set for the count for filled-in dates.

Weeks Not Starting on Monday

If you want to analyze weeks that start on a day other than Monday, you can also achieve this with a matches (advanced) filter on a date field as well (again, we are using the same week timeframe from the orders.created dimension group).

For example, if you want weeks that start on Sunday, you can use:

sunday - 7 days for 7 days, sunday - 371 days for 7 days

If you want your week to start on a different day, replace sunday with the day you want the week to start.

If it is the case that you always want your weeks to begin on a day of week other than Monday, you can also apply this condition at the LookML model level with a week_start_day parameter.

0 replies

Be the first to reply!