How to create a date field beastmode in Analyzer using 2 different date fields.

charles

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.

MarkSnodgrass

@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.

MarkSnodgrass

@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.

charles

Really, no takers? I thought the DOMO community liked a challenge!

MarkSnodgrass

@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.

charles

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!!!

MarkSnodgrass

@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.