8 Replies Latest reply on Mar 20, 2017 1:55 PM by toan.khuong

    Heathcare: Patient Census at Arrival time

    toan.khuong

      I have attached a mock up of a subset of data that includes each patient's [ED Arrival] and [ED Depart] date and time. I would like to to know the number of patients (Census) in the ED at the time of a patient's arrival time. So it would need to count the number of patients that include the patient's arrival time within their [ED Arrival] and [ED Depart] time frame.

       

      I'm not sure how to perform column calculations based on the current row condition. In Excel, I can do the calculation by using:

       

      =COUNTIFS(A:A,"<="&A2,B:B,">="&A2)

       

      note that Column A is [ED Arrival] and Column B is [ED Depart]

       

      Since I will be using Tableau to filter out patients with odd values, I would need the Census to recalculate (reason why I can't use Excel) after being filtered. My main goal is to find the average Census value per patient.

       

      I do not have server access, so can't use any SQL coding.

       

      Thanks

        • 1. Re: Heathcare: Patient Census at Arrival time
          Michael Someck

          Hi Toan,

           

          This method might be a bit convoluted, but hopefully it's somewhat helpful (or someone else has a better solution ). It may not be glamorous, but I was able to calculate what you're looking for by pivoting your data. The final result looks like this, where each row calculates the total number of active patients after an arrival or departure.

           

          Screen Shot 2017-03-17 at 5.57.59 PM.png

           

          To get there, I first pivoted the data:

           

          Screen Shot 2017-03-17 at 5.57.17 PM.png

           

          So the data then looks like:

           

          Screen Shot 2017-03-17 at 5.57.31 PM.png

           

          I then created a Calculated Field called Arrive/Leave that would count whether a person came or left:

           

          if [Pivot Field Names]="ED Arrival" then 1

          elseif [Pivot Field Names]="ED Depart" then -1

          END

           

          The final table then uses a table calc (running sum, table down) to track the number of "active" patients after each arrival and departure (green=arrival and red=departure).

           

          I've also attached the relevant workbook, but unfortunately it's v10.2.

           

          Michael

          2 of 2 people found this helpful
          • 2. Re: Heathcare: Patient Census at Arrival time
            Joe Oppelt

            To demonstrate what I did, I added patient IDs to your data (and just kept 10 rows to demonstrate what's happening.)  So I made my own excel file and a new data source.

             

            I have a parameter to let you select a date-time.  I'm guessing that you will be typing in a time at check-in.


            I run a calc that sets 1 or 0 if that patient is in the ED at the time entered.

             

            You can SUM up the calc or do whatever you need with it.

             

            See attached. (V9.3 for your convenience.)

            • 3. Re: Heathcare: Patient Census at Arrival time
              Joe Oppelt

              Sorry.  I added the wrong workbook.  Use this one.

              • 4. Re: Heathcare: Patient Census at Arrival time
                toan.khuong

                Hi Michael and Joe,

                 

                At first I thought I would need to use some kind of running total, but it would not achieve my end goal. Michael's idea to transpose the data was very creative, but I won't have the pleasure to pivot the data given there are plenty of other data columns I will need to use to filter out odd data.

                 

                Clarification to goal:

                I would like to calculate the census (patient population) at the time of each patient arrival. Then later summarized the average census over different date/time bins.

                 

                Thanks again for the help and ideas, I may use some of them in the future on other projects. If there are any other ideas, it would be greatly appreciated. I may be able to do this with an R connection, but would rather keep it restricted to Tableau, as my analyst will work with this later after I get everything setup.

                • 5. Re: Heathcare: Patient Census at Arrival time
                  Joe Oppelt

                  It seems like you're looking for some sort of data input here.

                   

                  Tableau is not made to do that.

                  • 6. Re: Heathcare: Patient Census at Arrival time
                    toan.khuong

                    I appreciate the help. I hoping to use R to do the calculations now, but my understanding of aggregated number is a bit complex for me. So I'm definitely not getting the results I'm expecting. Can someone help me adjust my code?

                     

                    SCRIPT_INT(

                    'date.time=data.frame(arrival=as.POSIXct(strptime(.arg1,"%m/%d/%Y %I:%M:%OS %p")),depart=as.POSIXct(strptime(.arg2,"%m/%d/%Y %I:%M:%OS %p")))

                    census=numeric(0)

                    for (i in 1:dim(date.time)[1]){

                      census[i]=dim(date.time[date.time$arrival[i]>=date.time$arrival & date.time$arrival[i]<=date.time$depart,])[1]

                    }

                    census',

                    ATTR([ED Arrival]),ATTR([ED Depart])

                    )

                    • 7. Re: Heathcare: Patient Census at Arrival time
                      Joe Oppelt

                      Toan -- The only people who are likely to see your last post at this point are Michael and myself.  I'm not well versed with R.  If Michael is also not, you're not going to get the audience you need for your latest question.

                       

                      If you need R help, start a new thread.  You can link the URL from this thread in there so the next person can refer back to this history.  Be sure to mention R and table calcs in your new thread title so that you attract people with the right skill set to jump in.


                      Before I go, I want to make sure I understand something correctly.  It seems like you want to capture the current ED population as each new patient gets checked in, as a real time front-end application.  This would require data updates from Tableau to your data source, which Tableau is not designed to do.  If I am misunderstanding what you are looking to do, then maybe it's worth pursuing.  I just want to prevent you putting in a lot of cycles trying to make Tableau do something it's not designed to do.

                      • 8. Re: Heathcare: Patient Census at Arrival time
                        toan.khuong

                        Hi Joe,

                         

                        I'm not trying to run tableau at real time, but I'm just trying to capture the perspective of each patient. We would love to reduce the wait time, and I'm sure the census will help us understand why some patients experience longer wait times. So we will most likely create monthly reports. The pivoting of data was very ingenious, but I'll be turning this over to someone else once I get this running smoothly. If I can keep it simple, it will cause less trouble.

                         

                        I'll "re-post" with your suggestions and point it back to this thread. Thanks again for your time and help.

                         

                        To continue following, the new thread can be found here: R calculation for Healthcare patient census.