How to filter complete weeks in the last quarter

[xin]

Knowledge Drop

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.

BigQuery:

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

AND

${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

AND

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

Postgres:

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

AND

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

This content is subject to limited support.