This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm hoping somebody can help me with this logic issue. I have 2 different things I'm trying to do. I'm trying to determine the datetimestamp for the end of an appointment and trying to filter for datetimes prior to that appointment. The dataset has multiple dates, locations, patients and room statuses. So grouping is an issue.
Task 1: Determine end of appointment timestamp: The dataset is grouped and ordered ascending by date, patient, location, appointments (rooms), roomtime (datetime). I'm concerned about calculating timestamps in relation to this main appointment:
READY MAIN APPT
Want to filter for all of the timestamps that occur after this room timestamp that are Room = "CHECK-OUT" OR "DISCHARGED" and sample the first record (or minimum) to determine when they left the actual MAIN APPT room.
Task 2: Determine which appointments immediately before this main appointment. (i.e. appointments that occur before ) Want to filter for all the timestamps that occur prior to this timestamp to determine which appointment is immediately before that could cause a delay. I can do my own analysis on this dataset, but wasn’t sure how to filter it based on the grouping of date, location, patient.
I've attached the sample data if anybody can help!
@RobertOdera Ha! Spoke too soon. I wasn't sure how to utilize the Multi-Row Formula tool like you did using the expression:
IF [Date]=[Row-1:Date] AND [sPatientNumber]=[Row-1:sPatientNumber] AND [sLocation]=[Row-1:sLocation] AND CONTAINS([Room],'CHECK-OUT') THEN 1
ELSEIF [Date]=[Row-1:Date] AND [sPatientNumber]=[Row-1:sPatientNumber] AND [sLocation]=[Row-1:sLocation] AND CONTAINS([Room],'DISCHARGED') THEN 1
ELSE 0 ENDIF
However, I still need to find only those timestamps that are "after" the main appointment time. The Check-out and Discharge is only when they leave the facility. They may have another appointment (room) after the main appointment? Any idea how to incorporate datetime into this to only allow the datetimes that are > than the main appointment time?