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 toany 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 toany 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 toany 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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive