-
1. Re: Time between clinical events - Healthcare
venkatesh.sharma May 11, 2015 10:03 AM (in response to Jim Beck) -
2. Re: Time between clinical events - Healthcare
Jim Beck May 11, 2015 10:34 AM (in response to venkatesh.sharma)Thanks for the reply.
I poorly stated the problem above - its more than just determining the difference between two dates.. First I need to identify all the patients who've had an initial office visit and then, for only those patients, determine whether they've had a surgery. When both those conditions have been satisfied, then I want to determine the length of time between that office visit and the subsequent surgery. So each patient has a unique identifier and office visits/surgeries have unique identification codes.
So I'm thinking - find all the patients that have had an office visit code and return that date. Then take that query result and use the patient Identifier to determine if they had a surgical code and then compare the dates from each to give me the datediff. I don't know how to do use one query result to use as the input of another query.
Jim
-
3. Re: Time between clinical events - Healthcare
venkatesh.sharma May 11, 2015 10:41 AM (in response to Jim Beck)if u are using Sql
write a sql query using "WITH " clause then u can use one query result to use as the input of another query
-
4. Re: Time between clinical events - Healthcare
pooja.gandhi May 11, 2015 10:50 AM (in response to Jim Beck)Hi Jim,
Your approach is exactly what you want to do to get the results you are seeking. First find all patients with an office visit by using an office visit code (I believe you may be dealing with ICD-10 codes). You can actually create 3 calcs.
1. IF [Office visit code] = '01' THEN [Visit Date] END - This will return all patients with the office code of 01 and their visit date
2. IF [Office visit code] = '01' and [Surgery Code] = '10' THEN [Surgery Date] END (or you can merge the 1st and 2nd calcs with an AND function.
3. DATEDIFF('day', [Surgery Date] , [Visit Date]) - This will give the time duration between both conditions.
This is just a starting point for you to get working on the problem. Ofcourse, your fields may differ in names etc. If this doesn't work, you can mock up a few rows of your actual data with your expected results.
Thanks,
PG.
-
5. Re: Time between clinical events - Healthcare
Jim Beck May 11, 2015 11:09 AM (in response to pooja.gandhi)Can this be done in Tableau or do I need to do this directly in the database using SQL?
-
6. Re: Time between clinical events - Healthcare
venkatesh.sharma May 11, 2015 11:10 AM (in response to Jim Beck)U can do this in tableau...
-
7. Re: Time between clinical events - Healthcare
pooja.gandhi May 11, 2015 11:12 AM (in response to Jim Beck)You can do this in Tableau directly just by creating calculated fields. If you need a starting point, you can try replicating your data in excel and I can build an initial version!
-
8. Re: Time between clinical events - Healthcare
Jim Beck May 11, 2015 11:42 AM (in response to pooja.gandhi)Thank you in advance for the help.
Attached is an xlsx file with the Patient Number, Date of service, CPT code and CPT description.
I'm looking to find the initial date of occurrence of cpt code 99201-99205 by patient number. When I find the patients with that code, I want to take the date of service associated with that code and compare it to the initial date of service of any cpt code that is less than 90000 (which will be the surgical codes - I'll have to refine this later to be more specific) for that patient. So, I'm looking for the number of days between the initial office visit (CPT 99201-99205) and their initial Surgery (CPT code<90000).
Thanks so much for the assistance.
JB
-
Tableau Sample Data.xlsx 323.5 KB
-
-
9. Re: Time between clinical events - Healthcare
pooja.gandhi May 11, 2015 12:19 PM (in response to Jim Beck)Hey Jim,
Not a full blown solution yet, but here is what I was talking about earlier. I created 2 calcs for each of the first dates (visit and surgery). Check out the calcs in the attached workbook. Is this what you are looking for?
So, in the example below, patient#17 had a first instance of CPT code greater than 99201 and less than 99205 associated with them on 10/30/2014 and the first instance of CPT code less than 90000 on 12/23/2014.
This is a version 8.3 workbook. I am not sure which version of Tableau do you have.
-
CPT Codes - PG.twbx 303.4 KB
-
-
10. Re: Time between clinical events - Healthcare
Jim Beck May 11, 2015 12:45 PM (in response to pooja.gandhi)That looks right. I use version 9. I'm at the dentist right now, so I'll be able to look at it more closely, this evening. I'm super excited to check this out. That's so much for the help.
JB
Sent from my iPhone
-
11. Re: Time between clinical events - Healthcare
pooja.gandhi May 11, 2015 1:04 PM (in response to Jim Beck)Oh in that case, someone may be able to help with LOD calculations for version 9 which I am not too familiar with yet. I won't be surprised if these calcs can be done in rather 1 step with LOD calcs.
-
12. Re: Time between clinical events - Healthcare
pooja.gandhi May 11, 2015 1:29 PM (in response to pooja.gandhi)Jim,
So I tried my hand at LOD calcs and I believe I got it working correctly. You can look at sheet 3 to verify the dates information by filtering a specific patient id and seeing their date of service for codes less than 90000 and the date of service for code between (99201 and 99205).
I created 5 calcs:
1. Less than 90,000: IF ([Cpt4]) <= 90000 THEN [Dt of Svc] END
2. Greater than 99201 and less than 99205: IF ([Cpt4]) >=99201 and [Cpt4] <= 99205 THEN [Dt of Svc] END
3. Visit date: {fixed [Patient #] : min([Greater than 99201 and less than 99205]) }
4. Surgical Date: {fixed [Patient #] : min([Less Than 90000]) }
5. Datediff: DATEDIFF('day', [Visit Date], [Surgery Date])
I then took the datediff function in the filters shelf to show only 'non-null' values by selecting sum > next > special > non-null values.
-
CPT Codes - PG - Version 9.twbx 307.7 KB
-
-
13. Re: Time between clinical events - Healthcare
Jim Beck May 11, 2015 3:56 PM (in response to pooja.gandhi)This is perfect Pooja! Where do you work at?
-
14. Re: Time between clinical events - Healthcare
pooja.gandhi May 12, 2015 5:21 AM (in response to Jim Beck)Hi Jim,
I am glad this worked for you! I used to work for the State Medicaid of Nebraska but then grabbed a rather nicer opportunity at a local software development company in Ohio! Where do you work?