1 2 Previous Next 15 Replies Latest reply on Dec 2, 2011 10:58 AM by Delia Caldwell

    Daily Census

    Delia Caldwell

      A database contains a year's worth of patient records with fields including admit date and discharge date and calculated length of stay. I'd like to create a running daily census given the length of stay for each record by day of the year. If I were doing this in excel, I'd create 365 columns, headed each by a day of the year and put a 0 or a 1 in each cell based on whether the patient was present on that day. Then I would add up all the rows across the year to get a total census by each day of the year.


      The problem is that excel can't handle the file size and stops running. What would my approach be in Tableau?

        • 1. Re: Daily Census
          Richard Leeke

          This thread discusses essentially the same problem - calculating queue depth.


          Basically you need to present the data to Tableau in the form of a set of dated events, where the events are admission or discharge,  An admission event increases the current occupancy count, a discharge event decreases it, like this:


          event_date, event, delta

          1/1/2011, admission, 1

          3/1/2011, discharge, -1



          You can have any other patient details you may need in each row.


          Then you can just use a Tableau quick table calculation to create a running sum of the [delta] column.


          You don't need to change your underlying data structure, you can use a custom SQL data expression to present the data to Tableau from your current records in that way.  The thread I linked to above explains how.

          • 2. Re: Daily Census
            Jonathan Drummey

            Thanks, I was just about to try to figure this exact same problem (patient census) myself.

            • 3. Re: Daily Census
              Delia Caldwell

              Richard, thanks for the input. As I understand it, what you are attempting to do is count events--patient admissions and patient discharges. But what I want to do is count census for every day of the year, regardless of whether someone comes or goes. How might I calculate the number of patients remaining on those days where there are no events? Your answer may address this and I am failing to comprehend. Apologies.

              • 4. Re: Daily Census
                Richard Leeke

                This method does let you get what you want - but with a little extra data preparation (so that you have deltas of zero on days when there are no admissions or discharges).


                If you could put some sample data together I can show you how.

                • 5. Re: Daily Census
                  Delia Caldwell

                  Richard, thanks. Here's a data set of unit start and unit end (admit and discharge). Will greatly appreciate your input.

                  • 6. Re: Daily Census
                    Richard Leeke

                    I've attached two versions of an example workbook showing what you can do relatively easily using the technique from that other thread I pointed you to.


                    I've included various ways of showing both the overall occupancy and the occupancy by ward.


                    One version uses the current version of Tableau (6.1), which is probably close to what you're after, but does suffer from the issue you mention of missing out days when there are no patient movements.


                    As I mentioned, you can get past this with a bit of extra data preparation work, to insert rows with a "delta" of zero for days without admissions or discharges.  You could do that either in your source data or with a bit of effort you can do it with custom SQL.  I talked a bit about what you need to do to achieve that in this thread - probably enough to let you see it's quite tricky.


                    But the good news is that it will get much, much easier with version 7, so the other workbook shows you what you will be able to do when version 7 comes out.  You would need to be on the version 7 beta program to be able to look at that just now, so I've included an image of a dashboard showing a few of the views in case you don't have access to the beta.


                    Version 7 has a couple of features which help with this analysis.  It will automatically fill in missing dates for you - so you don't have any days missing and it also supports area charts, which can be an effective way of showing this sort of data.

                    • 7. Re: Daily Census
                      Delia Caldwell

                      Richard, thanks very much. I had to do the pre-work in excel because I only have the desktop version that does not support the custom query (that I am aware of), but this worked, and I don't lose any days with an extremely large data set--every day during my date range there is activity. I don't have access to the beta but will look foward to it.


                      My next task is to take a census each day at noon and at midnight, and then create a histogram of patient censuses for each timestamp to see the variability across the year. Can I create date bins down to the hour for each of 365 days?


                      Thanks again for your interest.

                      • 8. Re: Daily Census
                        Richard Leeke

                        You can create custom SQL connections to Excel from Tableau desktop.


                        And yes, you can do the analysis down to whatever level of granularity you want - you just need a calculated field which truncates the datetime to the level you want.  To get midnight and midday, you just need a calculated field like this:



                        DATETIME(INT(FLOAT([Datetime]) * 2) / 2)

                        • 9. Re: Daily Census
                          Delia Caldwell

                          Thanks! I gather that this function returns the midnight before the date field of each record. But how does this expression help me know whether a patient was present at midnight? A patient conceivable could have been admitted after midnight and before the next midnight. I tried to write a calculated field that returns a 1 if Midnight is between my Admission event and my Discharge event. But even if Midnight is between the two events, how do I count for each midnight a patient stays (a patient may stay more than one midnight)? I tried something like this:


                          I used your function above to create a calculated field called "Midnight". Then I tried to create another calculated field that would return a 1 if the patient stay includes Midnight. Trying to plot with continuous Dates on one axis and counting Midnights did not return what I wanted. Any idea what I am doing wrong?


                          iif([Admission Time]>=[Midnight],iif([Ward End Date]<=[Midnight],1,0),0)

                          • 10. Re: Daily Census
                            Richard Leeke

                            Sorry, I probably should have explained slightly more what I was doing - and possibly even think about it some more.


                            My calculation truncates all timestamps to the previous midnight or midday.  So if you plot the total admissions minus discharges against the calculation I gave you, that will show you what the occupancy was at midnight and at midday - so you'll see two points on the chart for each day.


                            Thinking about it again, I think it would be better to add half a day to the calculated field, since the occupancy will be reported at the end of the 12 hour period, but the way I had the calculation it will be reported with the start of the period.  So modify the calculation like this and call it [Reporting Time]:


                            [Reporting Time] = DATETIME((INT(FLOAT([Datetime]) * 2) / 2) + 0.5)


                            An example will make this clearer.


                            Given this data:


                            Patient ID, Event Type, Event Timestamp, Reporting Time

                            1, Admission, 1 Jan 2011 09:00, 1 Jan 2011 12:00

                            1, Discharge, 1 Jan 2011 15:00, 2 Jan 2011 00:00

                            2, Admission, 1 Jan 2011 18:00, 2 Jan 2011 00:00

                            2, Discharge, 2 Jan 2011 18:00, 3 Jan 2011 00:00

                            3, Admission, 2 Jan 2011 10:00, 2 Jan 2011 12:00


                            You would get these occupancy figures:


                            Reporting Time, Occupancy

                            1 Jan 2011 12:00, 1

                            2 Jan 2011 00:00, 1

                            2 Jan 2011 12:00, 2

                            3 Jan 2011 00:00, 1

                            • 11. Re: Daily Census
                              Delia Caldwell

                              Thanks again, I used your expression for reporting time and do indeed get a representative chart. I suppose though, that since there very often could be an admission and a discharge of a patient in between the actual date stamp and reporting hour, it may be more accurate to make reporting hour as close to the actual date stamp as possible. So I tried this in place of your 0.5:


                              DATETIME((INT(FLOAT([Date]) * 2) / 2) + (DATEPART("hour",[Date] )/24))


                              Then with a bit more data work, crosstabbed to Excel, calculated hour(date) and filtered by midnight and noon.


                              This seems to best suit what I'm being asked to produce.


                              I appreciate your spending the time with me to do this--I've learned a lot!

                              • 12. Re: Daily Census
                                Richard Leeke

                                DATETRUNC('hour', [Date]) + 1/24


                                would be a slightly easier way to get the time at the end of the reporting hour.  Comes to the same thing (I think I didn't check my sums!).


                                I'm not quite sure I understand exactly what you're doing, or why you need the Excel cross-tab - but glad you've got to an answer you're happy with.

                                • 13. Re: Daily Census
                                  Delia Caldwell

                                  My thought was simply that I need to filter on the midnight hour since I generated essentially every reporting hour. However when I tried to do a filter on Hour(Reporting Hour) my running delta fell apart. Is there a way to assign a variable as the output of the table calculation? My understanding is that you can't do anything further with the table calculation to use in another calculation.

                                  • 14. Re: Daily Census
                                    Richard Leeke

                                    I'm fairly sure there will be a way to do what you want.


                                    There is a trick you can use to force the filtering to be applied after the table calculation, so that it only restricts what is shown rather than breaking the table calculation.  I described that in this comment on another thread.


                                    I'm not sure whether that is really the best way of doing what you want, though.  What sort of format of view are you trying to produce?  If you want a textual table with two rows per day then you may need to do that filtering trick (though I suspect it will miss rows if there were no admissions or discharges during the midnight hour - at leats until you have version 7).  If you want a graphical view showing the peaks and troughs of the occupancy then you may be better to display against a continuous time axis and don't bucket the times at all.

                                    1 2 Previous Next