Pulling the First Saturday of the previous 6 months

AKep
AKep Member
in Magic ETL

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!

Best Answer

  • rco
    rco Contributor
    edited November 2024 Answer ✓

    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.

    Randall Oveson <randall.oveson@domo.com>

Answers

  • ggenovese
    ggenovese Coach

    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
    AKep Member

    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
    rco Contributor
    edited November 2024 Answer ✓

    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.

    Randall Oveson <randall.oveson@domo.com>

Categories