Knowledge Drop

How to filter complete weeks in the last quarter

Userlevel 2
  • Looker Staff
  • 16 replies

Last tested; Jan 3, 2020

For some analysis, you might want to get only the completed weeks in the last quarter, for example, if the first day of Q4 2019 (2019-10-01) is Tuesday, then the start day should be 2019-09-30, and the last day of the quarter is 2019-12-29 (Sunday).

We can use the below SQL to create a yesno filter, the yes value will filter out all the dates of the complete weeks.


${date_dimension}>=date_trunc(date_add(date_trunc(current_date(), quarter), interval -1 quarter), ISOWEEK)


${date_dimension}<date_add(date_trunc(date_add(date_trunc(current_date(), quarter), interval -1 week), ISOWEEK), interval 1 week)


Redshift and Snowflake:

${date_dimension}>=date_trunc('week', dateadd('quarter', -1, date_trunc('quarter', current_date)))::date


${date_dimension}<dateadd('week', 1, date_trunc('week', dateadd('week', -1, date_trunc('quarter', current_date))))::date



${date_dimension}>=date_trunc('week', date_trunc('quarter', now())-interval '3 months')::date


${date_dimension}<(date_trunc('week', date_trunc('quarter', now())-interval '1 week')+interval '1 week')::date



This content is subject to limited support.                




0 replies

Be the first to reply!