Pulling the First Saturday of the previous 6 months

AKep

Hello, i'm trying to create an ETL filter formula that will pull the first Saturday of each month going back six months, but keep hitting errors. I've tried utilizing date_truncs with dayofweeks but no dice yet. Any assistance would be much appreciated!

rco

For a date column called "d", the filter formula would be:

dayofweek(d) = 7
and dayofmonth(d) < 8
and (year(today()) * 12 + month(today())) - (year(d) * 12 + month(d)) < (case when dayofweek(today()) = 7 or dayofweek(today()) < dayofmonth(today()) then 7 else 6 end)

This checks three conditions:

1. The day is Saturday
2. The day is in the first week of the month
3. The year*12 + months difference between the current date and the date in question is less than 7 if there has not yet been a Saturday in the month, or 6 if there has been.

The formula is simpler if you want anything in the last 6 months regardless of whether there has already been a Saturday in the current month:

dayofweek(d) = 7 and dayofmonth(d) < 8 and d >= today() - interval 6 month

But from your description I think the first formula is what you want.

ggenovese

You can use this formula and filter for 'INCLUDE'

— First Saturday of the Month for the Last Six Months
CASE 

WHEN DAY(`dt`) = DAY(DATE(DATE_FORMAT(`dt`,'%Y-%m-01'))+7-WEEKDAY(DATE(DATE_FORMAT(`dt`,'%Y-%m-01'))))

AND `dt` >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 Month)

AND `dt` <= CURRENT_DATE()

THEN 'INCLUDE'

ELSE 'EXCLUDE'

END

AKep

Unfortunately, this didn't come back with what I was looking for. I should probably add a bit more context. I have a "DATE" column that includes every Saturday of the year (2024-11-09, 2024-11-02, 2024-10-26, 2024-10-19, etc.). I am trying to get it to dynamically match/filter out for the Saturday dates of the last six months (2024-10-05, 2024-09-07, 2024-08-03, etc.)

rco

For a date column called "d", the filter formula would be:

dayofweek(d) = 7
and dayofmonth(d) < 8
and (year(today()) * 12 + month(today())) - (year(d) * 12 + month(d)) < (case when dayofweek(today()) = 7 or dayofweek(today()) < dayofmonth(today()) then 7 else 6 end)

This checks three conditions:

1. The day is Saturday
2. The day is in the first week of the month
3. The year*12 + months difference between the current date and the date in question is less than 7 if there has not yet been a Saturday in the month, or 6 if there has been.

The formula is simpler if you want anything in the last 6 months regardless of whether there has already been a Saturday in the current month:

dayofweek(d) = 7 and dayofmonth(d) < 8 and d >= today() - interval 6 month

But from your description I think the first formula is what you want.