6 Replies Latest reply on May 13, 2015 11:31 AM by Daniel Bunger

    Trending Hospital Census based on Admission and Discharge dates

    Daniel Bunger

      Commonly, hospitals look at their in-house census by Length of Stay (LOS) groups.  That is, at a given time, how many patients have been in the hospital for 1 day, 2-5 days, 6-10 days, 11-20 days, and >20 days.  This information can help administrators to make staffing decisions or identify bottlenecks/variation in care.

       

      I am trying to visualize the in-house census by LOS group over time using admission and discharge dates.  I can do this for a single data using a parameter.  A calculated field then identifies patients admitted prior to the parameter date but were discharged after the parameter date and then calculated the LOS as the difference between the admission date and the parameter date.

       

      Unfortunately, this only allows for a census at a single point in time.

       

      Is there a way of visualizing multiple values for this parameter, say, with a temp table? Ideally, i'd be able to look at the number of 2-5 day cases in the hospital on 1/1/14, 1/2/14, 1/3/14, 1/4/14, etc...

        • 1. Re: Trending Hospital Census based on Admission and Discharge dates
          Tom W

          There's a couple of approaches, it depends on your data source type and structure.

          One approach is to basically duplicate your data so every day has a list of patients staying on that day.

           

          Post some more details about your data source, structure and a Tableau Packaged Workbook would be helpful as well.

          • 2. Re: Trending Hospital Census based on Admission and Discharge dates
            Daniel Bunger

            I'm using an oracle database.  The relevant fact table gives a row for each inpatient encounter.

            • 3. Re: Trending Hospital Census based on Admission and Discharge dates
              Tom W

              Why not use Custom SQL to cross join your fact level data with a list of dates so you end up with one row per day?

               

              There may be some methods to achieve in Tableau; Please upload a packaged workboook with some sample data.

              • 4. Re: Trending Hospital Census based on Admission and Discharge dates
                Brent Malak

                The simple responses are... because it is too many rows of data, isn't how the data is stored or provided from the source systems, and requires implementing another source of content.  Sometimes you get what you get, not necessarily what you want.  Regardless...can this be done without adding another source?

                 

                Daniel,

                I am working on the same problem right now, very similar to hospital stays (actually, acute and post-acute care LOS.)

                This would give me a caseload or census view of data that is currently loaded, stored, and delivered with episode/case begin and end dates.

                 

                First, I've searched through countless posts in the forum and by Tableau on this and cannot identify a solution.  Maybe it is semantics, as to how to formally define the issue that causes part of the problem.  Many of these are unanswered.

                 

                Tom, using a cross-join and custom SQL from an already existing data source does not solve this problem.  I have data in a defined "book-end" format, that is, a row of data with an admit date and a discharge date.  It's one row of data.  To import another data source to pull in each date extensively "lengthens" the number of records.  If a hospital stay or acute case is over one hundred days, I've effectively multiplied my rows by 100.  Sure, some of it is reduced in width because columns may be dropped, but it is still not efficient.

                 

                I wanted to show a sample data set to show the concept.  Realize, that in these instances, when a "stay" or duration is longer, the number of rows gets longer.  Hmmm...well, I can't seem to include an attachment of the data set or viz that I created...

                 

                   Original set

                CaseBeginEndColor
                A5/1/20155/5/2015Red
                B5/2/20155/6/2015Green
                C5/4/20155/4/2015Blue
                D5/4/20155/8/2015Blue
                E5/3/20155/7/2015Red
                F5/6/20155/10/2015

                Green

                 

                 

                    Desired set transformation WITHOUT having to use a cross-join or additional source...Can it be done using parameters, calculated field, or another method?

                CaseDateColor
                A5/1/2015Red
                A5/2/2015Red
                A5/3/2015Red
                A5/4/2015Red
                A5/5/2015Red
                B5/2/2015Green
                B5/3/2015Green
                B5/4/2015Green
                B5/5/2015Green
                B5/6/2015Green
                C5/4/2015Blue
                D5/4/2015Blue
                D5/5/2015Blue
                D5/6/2015Blue
                D5/7/2015Blue
                D5/8/2015Blue
                E5/3/2015Red
                E5/4/2015Red
                E5/5/2015Red
                E5/6/2015Red
                E5/7/2015Red
                F5/6/2015Green
                F5/7/2015Green
                F5/8/2015Green
                F5/9/2015Green
                F5/10/2015Green

                +

                The final result could look like this...

                book-end dates transformed.gif

                • 5. Re: Trending Hospital Census based on Admission and Discharge dates
                  Tom W

                  Here's a run down of a method using a join in Tableau - http://kb.tableau.com/articles/knowledgebase/combining-start-end-dates-into-single-axis

                  It requires you to create an Excel Worksheet of all dates then join that to your second Excel workbook containing your data. To use this method you would need to use the Excel Legacy Connector and obviously be using an Excel Data Source.

                   

                  If you're using a database source, you're going to have to do this as a cross join or use a similar method to above where you have a list of all dates for the year and you left join to your actual data. I.e.

                   

                  SELECT a.Date, d.StartDate,d.EndDate,d.Classification

                  FROM alldatesinyear a

                  LEFT JOIN mydata d on a.date between d.StartDate and d.EndDate

                   

                  Yes, you're going to end up with a lot more rows which is going to take longer to pull down the data and create larger extracts. But unfortunately it's exactly what you're asking for.

                   

                  You could reduce this by using the SQL to pre-aggregate your metrics so you don't need all the raw data. Example;

                   

                  SELECT a.Date,d.Classification, COUNT(DISTINCT d.ID) ObservationCount

                  FROM alldatesinyear a

                  LEFT JOIN mydata d on a.date between d.StartDate and d.EndDate

                  GROUP BY a.Date, d.Classification

                   

                  This would pull you an aggregated table of all dates along with the classification and the count of records seen on that date. You may need to cross join your 'all dates' and classifications here to make sure you effectively get all combinations, I'm just working through the concept.

                  1 of 1 people found this helpful
                  • 6. Re: Trending Hospital Census based on Admission and Discharge dates
                    Daniel Bunger

                    This is what I figured would be the only solution. Unfortunately, this solution:a) results in some very slow processing, and b) limits analysis to whole days (effectively midnight censuses) unless you were to dramatically increase the number of rows (and processing time) by multiplying that alldateinyear table by 24 for each hour in the day.

                     

                    Thanks for your help!