Filter On Current Week Of Year

[LookerExperts]

Knowledge Drop

Last tested: Jan 21, 2019
 

Use a custom filter:

${week_of_year_dimension} = ceiling(diff_days(date(extract_years(now()),01,01),now())/7)

Here we're getting the difference in days between January first of this year and today and dividing that by 7 to get the number of weeks. We use ceiling to round it up so we get the current week.

This content is subject to limited support.                

[JPL1]

This expression is not entirely correct.It does not take into account the day the year begins. The ISOWEEK concept must be taken into account. The first week is one that has a Thursday in it.What I have done is a bit more complex but it works.The concept behind it is the following:If you divide the number of days that have passed in the year by 7 and pay attention to the decimals, you can deduce the day the year began by comparing it to the day of the record.With that data you can add or subtract the days to January 1 to balance the weeks.

DATE		week day	week index	dif	output
20/03/2023	2	mon	0	79	11,2857143
21/03/2023	3	tue	1	80	11,4285714
22/03/2023	4	wed	2	81	11,5714286
23/03/2023	5	thu	3	82	11,7142857
24/03/2023	6	fri	4	83	11,8571429
25/03/2023	7	sat	5	84	12
26/03/2023	1	sun	6	85	12,1428571

This table can help to see that the decimals are repeated

I hope to be helpful

${YOUR_DATE_FIELD_week_of_year} = 
case(
  when(abs((${YOUR_DATE_FIELD_day_of_week_index}+1)-(((diff_days(date(extract_years(now()),01,01),now())/7) - (floor(diff_days(date(extract_years(now()),01,01),now())/7)))*7))<4,ceiling(((diff_days(date(extract_years(now()),01,01),now()))+((${YOUR_DATE_FIELD_day_of_week_index}+1)-(((diff_days(date(extract_years(now()),01,01),now())/7) - (floor(diff_days(date(extract_years(now()),01,01),now())/7)))*7)))/7)),
  when(((${YOUR_DATE_FIELD_day_of_week_index}+1)-(((diff_days(date(extract_years(now()),01,01),now())/7) - (floor(diff_days(date(extract_years(now()),01,01),now())/7)))*7))>3,ceiling(((diff_days(date(extract_years(now()),01,01),now()))+(((${YOUR_DATE_FIELD_day_of_week_index}+1)-(((diff_days(date(extract_years(now()),01,01),now())/7) - (floor(diff_days(date(extract_years(now()),01,01),now())/7)))*7))-7))/7))
  ,floor(((diff_days(date(extract_years(now()),01,01),now()))+(((${YOUR_DATE_FIELD_day_of_week_index}+1)-(((diff_days(date(extract_years(now()),01,01),now())/7) - (floor(diff_days(date(extract_years(now()),01,01),now())/7)))*7))+7))/7))