Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a list of data that I want to evaluate on just the minimum date value only. I have a list of people who have had tests performed, and these tests should be performed every day on each person during their visit. I want to be able to tell if the person had a test performed within 8 hours of arriving for their visit. If a person had has had multiple tests per visit, how do I just count the first test, and see if that was within 8 hours or arrival? Here's an example of the data:
Visit ID | Person ID | Arrival Date/Time | Test Date/Time | Interval | Less than 8 hours? | |
28704007 | 1148967 | 1/12/2015 20:54:58 | 1/12/2015 21:39:52 | 0.031180556 | Yes | |
28704007 | 1148967 | 1/12/2015 20:54:58 | 4/12/2015 2:42:55 | 2.241631944 | No | |
28704007 | 1148967 | 1/12/2015 20:54:58 | 5/12/2015 3:13:27 | 3.262835648 | No | |
28693342 | 217067 | 1/12/2015 21:02:00 | 1/12/2015 22:46:04 | 0.072268519 | Yes | |
28698114 | 431145 | 1/12/2015 21:23:22 | 2/12/2015 1:55:14 | 0.188796296 | Yes | |
28706266 | 1152055 | 1/12/2015 21:47:55 | 4/12/2015 11:01:40 | 2.551215278 | No | |
28698465 | 666648 | 1/12/2015 21:55:00 | 1/12/2015 23:35:57 | 0.070104167 | Yes | |
28670716 | 874611 | 1/12/2015 22:16:05 | 2/12/2015 4:53:14 | 0.275798611 | Yes | |
The count that I would want to achieve here is 5. Five people had a test within 8 hours of arrival (Person 1148967 had 3 tests, but the first one was performed within 8 hours). One did not. Do I perform this using the Aggr function? What would this look like? What if I create a new table in the load script with FirstValue?
Many thanks,
Glen
My bad?
Count({<[Less than 8 hours] = {Yes}>} [Person ID])
You can use the below
count({<Person ID = {"=interval(test time - Arrival Time,'HH')<8"}>} Person ID )
If you already calculated "Less than 8 hours" ??
Count({<[]Less than 8 hours = {Yes}>} [Person ID])
My bad?
Count({<[Less than 8 hours] = {Yes}>} [Person ID])
Perhaps I was overthinking this..