Forum Discussion
Helper ICreate Date Time and Shortest Wait Time Measures
I am working on a dashboard that provides various Date and Time outputs.
I need to create a measure or two that will show the 'Date and Time' (in one) associated with the 'Shortest Wait Time' (in the other) during that day.
I need the same for the 'Week' and the 'Month'.
Desired Output -
Sample Data -
| TicketNumber | TicketDate | InTime | OutTime | WaitTime (hh:mm:ss) |
| S28 | 6/3/2019 | 7:03 AM | 7:36 AM | 0:33:16 |
| S111 | 6/3/2019 | 7:03 AM | ||
| S112 | 6/3/2019 | 7:04 AM | 7:28 AM | 0:24:29 |
| S210 | 6/3/2019 | 7:04 AM | 1:48 PM | 6:44:29 |
| S213052 | 7/8/2019 | 2:06 PM | 2:18 PM | 0:11:54 |
| S14951 | 7/8/2019 | 2:07 PM | 2:22 PM | 0:15:33 |
| S213053 | 7/8/2019 | 2:07 PM | 2:33 PM | 0:25:47 |
| S213054 | 7/8/2019 | 2:08 PM | 2:46 PM | 0:38:36 |
| S233696 | 8/30/2019 | 4:07 PM | 4:27 PM | 0:20:27 |
| S233697 | 8/30/2019 | 4:08 PM | 4:34 PM | 0:25:49 |
| S110922 | 8/30/2019 | 4:08 PM | ||
| S233698 | 8/30/2019 | 4:09 PM | 4:28 PM | 0:19:47 |
Current Filter Matrix in Dashboard -
11 Replies
- lbendlin
Super User
you may want to rethink your data structure. As it is it cannot handle tickets that are open across day boundaries. "Ticket Date" should be eliminated, and "In Time" and "Out Time" should be renamed "In DateTime" and "Out DateTime".
PhineasTAnd then what?
Also, I don't understand your comment..."As it is it cannot handle tickets that are open across day boundaries."
I merely want a card that contains the date and time (just the date in MM-DD-YYYY form will do), and another card that contains the shortest wait time on that date and the time of day of that Shortest wait time.- lbendlin
Let's say the "In Time" for a ticket is 11:55pm and the "Out Time" is 02:05am.