I am not able to open your workbook.
You could create a flag (Y/N) that indicates if it is a distinct admission or not. This could be done by matching the patient ID and checking if the date is continuous from the first day of the admission.
Then you could count the Y flags by date to report number of distinct admissions.
Hi Jason! This is an interesting problem! I couldn't open your packaged workbook for some reason, but let me offer a suggestion:
- Build a view that has ID# along rows and Admission Date (discrete, days) along columns.
- Create a field that equals adds 1 if it's the first index or if the previous date is not the immediately preceding day. Maybe something like this:
PREVIOUS_VALUE(1) + IIF(DATEDIFF('day', LOOKUP([Admission Date], -1), MIN([Admission Date]) > 1, 1, 0)
- Drop that field into the text. Hopefully it'll work as a rolling sum of the noncontiguous admission dates, which means that the last cell in each row should be the correct sum.
- If that works, let me know and we can use some tricks to clean up the view so we only see the last value.
Thanks for the reply.
This looks promising. I wasn't able to make the calculation - I suspect you would know if you could open the workbook. The original workbook that I saved in version 10 - I wonder if that is the problem. I saved it in version 9.3 and uploaded it again. Let me know if you can't open this file.
Oh, I should have thought to open it in 10! Silly me. I can just work with the original file, then. I got it to look like this:
The formula I gave you before had a couple of errors, so here's the one that calculates the number of initial admissions:
PREVIOUS_VALUE(1) + ZN(IIF(DATEDIFF('day', LOOKUP(MIN([Admitdt]), -1), MIN([Admitdt])) > 1, 1, 0))
When you put this in here and make it compute along the date, it'll give you a running sum of the initial admissions. However, if you use the actual date field, you get weird table calculation behavior, so I had to convert it to a string first (DateStr here). Then, I used a table calculation filter that keeps only the last date for each ID#. And voila!
You can just hide the DateStr dimension if you'd like to clean it up. If you need to further roll this up into groups of ID#s, you'll have to add more table calculations on that take window sums.
Tableau Forum Claims Question.twbx 275.4 KB
Thank you so much, David. Beautiful.
You're very welcome!