Sorry, I can't post the workbook as it has protected health information. Let me see if I can copy the structure and put some dummy data on it.
I think I can give you some ideas without requiring you to put together some anonymized data.
To do this sort of analysis in Tableau you need to have a record set with the all the dimensional values that you'd need in the view (i.e. on Rows, Columns, Pages, or Marks Card), which in this case would be at least the Date/Time and MRN. There are a couple of ways to go about this:
- Create a scaffold data source that has all the combinations, use that as the primary data source, then use Tableau Data Blending to add your two secondary data sources. There are a variety of ways to go about this, from doing it all in queries in the underlying source, to Custom SQL, to a bit of custom query combined with Tableau's domain padding.
- Union your two data sources together.
Thanks Jonathan, I got it and I am creating the scaffold source by dumping both data sources into an excel table. If I understand this correctly, then I should join this with the two original data sources through data blending.
Now, the part that I am hanging is how to create the filter or calculated field to show those records of patients that had a restraint episode within 2-8 hrs. of a fall episode (for example). I am not that good on nested IFF statements.
If you have all of the fields from both data sources in your scaffold source, then you wouldn't necessarily need a data blend, because you'd have all the data.
Doing the comparison across rows of the data definitely gets complicated. I use two different approaches:
- If the problem area is sufficiently generalizable (like identifying patients with a primary or secondary diagnosis of COPD), then I'll add a subquery to underlying data source that adds one or more columns to the Tableau data source to flag the necessary condition(s). Though this can take more work to set up, it can be easier to maintain and more performant than table calcs.
- Use Tableau table calcs to generate a flag. Given an Episode Type and DateTime, you can use something like:
IF MIN([Episode Type]) == "Fall" AND LOOKUP(MIN([Episode Type],-1) == "Restraint" AND DATEDIFF('hour',MIN([Episode DateTime]),LOOKUP(MIN([Episode DateTime]),-1)) <= 8 THEN
"Restraint w/in 8 hours"
This would need a unique identifer of each episode as part of the addressing (Compute Using), and be partitioned on each patient.