How to create a date field beastmode in Analyzer using 2 different date fields.
I am trying to use different parts of 2 different date fields to make a new date field called Calendar_shipDT.
Schedule_shipDt is some time in the future, and Potential_shipDT is any potential ship date, past or present. I just need the WEEKDAY (1-7) from the Potential_shipDT. Say that day of the week is Wednesday (2023-2-8). I need that Wednesday to be the Wednesday that comes on or after the Schedule_shipDT.
For example, if the Schedule_shipDT is 2023-2-23 (a Thursday) and the weekday of the Potential_shipDT is Wednesday, I need the Calendar_shipDT to be the Wednesday following the Schedule_shipDT. In this example, the Calendar_shipDT would be 2023-3-1 (6 days after the Schedule_shipDT on a Wednesday), because the weekday of the Potential_shipDT came before the weekday of the Schedule_shipDT.
Another example, if the Schedule_shipDT is 2023-2-21(Tuesday) and the Potential_shipDT is 2023-2-8 (Wednesday), since the weekday of the Schedule_shipDT comes before the weekday of the Potential_shipDT, the new Calendar_shipDT would be 2023-2-22.
Best Answers
-
@charles It looks as though your calendar_shipDT is really just determined by looking at the schedule_ShipDT and seeing which day of the week it is and making the calendar_shipDT be the appropriate Wednesday. Here is what I put together that would accomplish this:
CASE /* if the day of week of the schedule ship date is Wednesday, use that day */ WHEN `dt` = 4 THEN `dt` /* if the day of week of the schedule ship date is Sun, Mon, Tue, use the upcoming Wednesday */ WHEN `dt` < 4 THEN DATE_ADD(`dt`, INTERVAL (4-DAYOFWEEK(`dt`)) day) /* if the day of week of the schedule ship date is Thur, Fri, Sat, use the Wednesday in the following week */ WHEN `dt` > 4 THEN DATE_ADD(`dt`, INTERVAL (11-DAYOFWEEK(`dt`)) day) END
You would replace dt with your schedule_shipDT fieldname.
This would result in data that looks like this:
Let me know if that doesn't work for you.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
@charles Ha! Glad it worked for you. If you mark the answer as accepted, that will help others in the community that may run into a problem like this in the future.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
Really, no takers? I thought the DOMO community liked a challenge!
0 -
@charles It looks as though your calendar_shipDT is really just determined by looking at the schedule_ShipDT and seeing which day of the week it is and making the calendar_shipDT be the appropriate Wednesday. Here is what I put together that would accomplish this:
CASE /* if the day of week of the schedule ship date is Wednesday, use that day */ WHEN `dt` = 4 THEN `dt` /* if the day of week of the schedule ship date is Sun, Mon, Tue, use the upcoming Wednesday */ WHEN `dt` < 4 THEN DATE_ADD(`dt`, INTERVAL (4-DAYOFWEEK(`dt`)) day) /* if the day of week of the schedule ship date is Thur, Fri, Sat, use the Wednesday in the following week */ WHEN `dt` > 4 THEN DATE_ADD(`dt`, INTERVAL (11-DAYOFWEEK(`dt`)) day) END
You would replace dt with your schedule_shipDT fieldname.
This would result in data that looks like this:
Let me know if that doesn't work for you.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
It friggen worked!!!! I had to modify it a little, but your idea was much more simple than what I was trying to do, which kept crashing DOMO. Mark, you seriously rock!!!
1 -
@charles Ha! Glad it worked for you. If you mark the answer as accepted, that will help others in the community that may run into a problem like this in the future.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 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