I have read through most of the open close time FAQ forum, but I haven't seen anything that quite solves my problem. I have a data table that for each row displays an employee's clock in/clock out time and dept name (Pre-Op, OR, Recovery). I have another table that displays a patient's Pre-Op time in/out, OR time in/out, and Recovery time In/Out. The date range is YTD.
I need to plot, at either 5 or 15 minute intervals (whichever is easier), the average number of patients in the OR and the average number of OR employees on the clock in the same graph. The average though would be the total number of employees clocked in at a given time interval/Number of days the facility is open and the number of patients in the OR at a given time interval/Number of days the facility is open. A distinct count of date would work, but tableau won't allow you to use that calculation across blended data sources. I was able to graph employees and patients on separate graphs using calculated fields for each time interval, but there's just no way to combine them.
The excel file includes a worksheet that shows the desired finished product. Also, I have multiple facilities, so I would need to filter on facility id.
I attached an excel file with test data that represents what I'm using. Also, I'm pulling the employees and patient data directly from a database. I just mocked up the excel file, so I could publish the question. The "Created Time" worksheet is just my attempt at trying some of the methods I've seen on the forum.
Sorry to make this harder, but I know almost nothing about writing SQL code. So... any explanation that includes writing custom SQL code will need to be extremely detailed. I would prefer not to use it altogether if possible.
Any help here would be greatly appreciated!
Test Data.xlsx 155.5 KB