Adjusting "Tomorrow's" date if tomorrow is a holiday
I have a dataset we can call 'jobs' which has one row for each job on our calendar. Row has a column that says if the job is occuring on a holiday or not. I want to filter on the individual card levels based on if there is a holiday tomorrow, 2 days from now, 3 days from now, etc.
Job # | JobDate | Holiday
1 | 9/3/18 | True
2 | 2/1/18 | False
3 | 5/4/18 | False
I am using this code as a filter on many cards to show "Tomorrow's jobs" or Sat-Mon in the case of Friday. However, I need to adjust this so that if tomorrow is a holiday to show both tomorrow and the next day. If the holiday happens on Monday and today is Friday I need to show all jobs from Sat-Tues.
-- When 'True', show tomorrow's jobs except on Fridays display upcoming Sat-Mon jobs, on Saturday's display upcoming Sun-Mon jobs.
case
when
(DAYOFWEEK(CURRENT_DATE())=1)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Sun show Mon
when
(DAYOFWEEK(CURRENT_DATE())=2)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Mon show Tue
when
(DAYOFWEEK(CURRENT_DATE())=3)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- on Tue show Wed
when
(DAYOFWEEK(CURRENT_DATE())=4)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Wed show Thur
when
(DAYOFWEEK(CURRENT_DATE())=5)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Thur show Fri
when
(DAYOFWEEK(CURRENT_DATE())=6)
-- Change to 4 when holidays on Monday. 3 When Normal week.
and (DATE(`JobDate`) <= DATE(ADDDATE(CURRENT_DATE(), INTERVAL 3 DAY)))
and (DATE(`JobDate`) > DATE(ADDDATE(CURRENT_DATE(), INTERVAL 0 DAY)))
then 'True'
-- on Fri show upcoming sat, sun, mon
when
(DAYOFWEEK(CURRENT_DATE())=7)
and (DATE(`JobDate`) <= DATE(ADDDATE(CURRENT_DATE(), INTERVAL 2 DAY)))
and (DATE(`JobDate`) > DATE(ADDDATE(CURRENT_DATE(), INTERVAL 0 DAY)))
then 'True'
-- on Sat show upcoming sun, mon
else 'False' end
Thank you in advance for the help!
Comments
-
Hello!
You will need to use the LEAD () OVER() or LAG () OVER() beastmode functions to accomplish this. Because we need each comparison to happen on the same row. These two functions will allow us to do this.
For example
CASEWHEN `Holiday` = 'True' THEN 'Holiday Today'
WHEN LEAD (`Holiday`,1,0) OVER( ORDER BY `date`) = 'True' THEN 'Holiday Tomorrow'
WHEN LEAD (`Holiday`,2,0) OVER( ORDER BY `date`) = 'True' THEN 'Holiday In 2 Days'WHEN LEAD (`Holiday`,3,0) OVER( ORDER BY `date`) = 'True' THEN 'Holiday In 3 Days'
ELSE 'No Holiday in the Next 3 Days'
END
(I always make the mistake of forgetting to add a space between LEAD and ())
Cheers!**Say “Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive