[Solved] Week of year over Week of year comparison

Looker Q&A Looker
1

Hi,
We very often track our metrics by Week of year and I’d like to create table where I will be able to compare values with the previous year.

But also I’d like to display only last X week numbers of year based on current week number.

Let’s say it’s week 4, year 2019 and I want to display only last 3 week numbers: 4, 3, 2 for 2018 and 2019.

32.png
32.png391×108 8.05 KB

The next week I will want to see only week numbers: 5, 4, 3 for 2018 and 2019

38.png
38.png466×108 11.4 KB

I was thinking about custom filter but there is no extract_week function.

How would you approach this? I’ll appreciate any suggestions.

Thanks

2

There’s some insight on using a custom filter for this use case here: https://help.looker.com/hc/en-us/articles/360001189687-How-to-do-Period-over-Period-Analysis, which it sounds like you were already thinking about.

If you already have a week num dimension (you can also use the week_of_year timeframe), then you won’t need to use extract_week, right? You can just directly reference that dimension which contains the already extracted weeks, I think.

1 Like
3

Thanks a lot. You’re right and that’s what I am already using.

I was able to solve this by adding new dimension group on current date and then compare this value to any week of year value in custom filter

dimension_group: current {> type: time> timeframes: [time, date, week, month, day_of_week, hour_of_day, raw, week_of_year]> sql: current_date ;;> }

1 Like
4

Hello @izzymiller ! I want to know how can I add a filter on a week of a specific year? For example i need data of Week 25 of the year 2018, how do I do that?

5

@psidhu I would create two separate dimensions - one for year and one for week number and put both filters on bc I think the experience for the end user will be cleaner. However you could concatenate week and year into a single dimension if you wanted with something like

sql: year(date_dim) || '-' || week(date_dim)