Date/Time BETWEEN or IF/THEN/ELSE logic for COVID Testing

[JimAdlhochVPH]

I have searched the posts for the correct date/time BETWEEN or IF/THEN/ELSE logic for the task at hand and have not found anything that will meet the current need. We are using Qlik Sense.

We need to list the patients that have had COVID-19 testing during the two twelve hour shifts at the hospital. Each shift, AM (7am-7pm), and PM (7pm-7am), provides a listing of patients testing during their shift.

1. For the AM End of Shift Report, populate a table listing the tests performed (using COVID testing order date/time) on their shift between MM-DD-YYYY 06:00:00 a.m. and MM-DD-YYYY 05:59:59 p.m. on the same day.

2. For the PM End of Shift Report, populate a table listing the tests performed (using COVID testing order date/time) on their shift between MM-DD-YYYY 06:00:00 p.m. and MM-DD-YYYY 05:59:59 a.m. the following day (crossing over midnight).

3. Each of these tables has patient attributes (name, DOB, etc.)

4. The AM End of Shift table will be sent via nPrinting at 06:05:00 p.m. (the extra five minutes for order date/times to get to the data warehouse)

5. The PM End of Shift table sent via nPrinting at 06:05:00 a.m.

I would be fine with an IF/THEN/ELSE where using the logic described above. But, it’s the crossing over midnight for the PM Shift report that has me stumped.

Are there other posts, or logic/syntax gurus out there who could help? Thanks!

[rwunderlich]

The best approach would be to use IntervalMatch
https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPre...

Another way is to calculate if a specific patient timestamp falls within a range.  (I was unclear if your shifts were 7-7 or 6-6, you gave both times above. I'll use 6-6 in this example).

A timestamp is a Date (whole number) + Time (fraction of a day).  So if you have the testing timestamp you could assign it to a recent shift like this:

count({<TestTime={">$(=Today()-1+MakeTime(18)) <$(=Today()+MakeTime(6))"}>}PatientId)  // Evening shift

count({<TestTime={">$(=Today()+MakeTime(6)) <$(=Today()+MakeTime(18))"}>}PatientId). // Day shift

-Rob

[JimAdlhochVPH]

Rob - thank you! I'll try out the code.

Sorry for the confusion about the times. The shifts are 7-7, but each needs the report at 6:00 to prepare for the change of shift handoff at 7:00.