Knowledge Drop

How to filter complete weeks in the last quarter

  • 7 July 2021
  • 0 replies
  • 57 views

Userlevel 2
  • Looker Staff
  • 13 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.

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.                

 

 

 


0 replies

Be the first to reply!

Reply