6 Replies Latest reply on Aug 27, 2016 9:39 AM by David Li

# Help with creating a calculated field to aggregate sequential dates for hospital admissions

I would love some help with creating a calculated field that will aggregate hospital admissions from a list of sequential dates.

In the below example - I created a visual of total hospital days that a patient has been admitted by using a COUNTD for the dates of admission.

I would like to create a visual that counts the total number of actual admissions from the listed dates.  For the below example, this patient had 7 hospital days but 3 distinct admissions - one from 12/14/2011-12/16/2011, next from 6/11/12-6/15/12 and the final from 3/31/13-4/1/13.

QUESTION: How do I create a calculated field that counts the number of distinct admissions by adding up sequential dates? (Packaged workbook attached)

Thank you!

Jason

• ###### 1. Re: Help with creating a calculated field to aggregate sequential dates for hospital admissions

Hi Jason

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.

• ###### 2. Re: Help with creating a calculated field to aggregate sequential dates for hospital admissions

Hi Jason! This is an interesting problem! I couldn't open your packaged workbook for some reason, but let me offer a suggestion:

1. Build a view that has ID# along rows and Admission Date (discrete, days) along columns.
2. 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)

3. 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.
4. If that works, let me know and we can use some tricks to clean up the view so we only see the last value.
• ###### 3. Re: Help with creating a calculated field to aggregate sequential dates for hospital admissions

Hi David.

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.

Jason

• ###### 4. Re: Help with creating a calculated field to aggregate sequential dates for hospital admissions

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:

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.

• ###### 5. Re: Help with creating a calculated field to aggregate sequential dates for hospital admissions

That's it!!

Thank you so much, David. Beautiful.

Jason

• ###### 6. Re: Help with creating a calculated field to aggregate sequential dates for hospital admissions

You're very welcome!