Pulling the First Saturday of the previous 6 months
Best 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>
1
Answers
-
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'
END0 -
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.)
0 -
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>
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive