4 Replies Latest reply on Sep 22, 2016 7:52 AM by Dan Gordon

    Question about Census calculation

    Dan Gordon
    Dan Gordon Sep 20, 2016 7:04 AM

      Hello all,

       

      I am new to Tableau having only used for about 80 Days.  I feel that I have just scratched the surface on what this can do.   Here is my problem:

       

      I am trying to calculate daily census.  I know the calculation is Previous Day Census + Present Day Admissions-Present Day Discharges.  This would seem like a fairly straight forward calculation.  However, I cant figure out two things:

      1) How to tell tableau what the calculated census was from the previous day and

      2) How to enter the starting census for the system to calculate off of going forward.

       

      For example. lets say we have the following (fictional):

       

      July 1, 2015 - Daily Census

      2A -      24

      2B -      15

      3A -      09

      4A -      23

      4B -      12

      PEDS - 17

       

      Now I want to display what the census is for July 1 2016. I know what each day's admissions and discharges were for the time period between the two dates, but how does Tableau calculate this?

       

      Let me also add that I am relatively new to SQL as well.  I am learning all this as fast as I can, but this problem has me stumped.  If this has already been discussed in another forum. please point me there and I will be happy to research further.

       

      Thank you for your time and attention and I look forward to one day being able to help someone else.

       

      Regards,

       

      Dan Gordon

      Frederick Memorial Hospital

      Dgordon@fmh.org

        • 1. Re: Question about Census calculation
          Michael Johnston
          Michael Johnston Sep 20, 2016 9:19 AM (in response to Dan Gordon)

          Hey Dan,

          I have been using Tableau for slightly a longer period of time. I actually bought some reasonable priced courses on UDEMY to help me out. Can you post your workbook so I can see and try to create a calculated field?

          • 2. Re: Question about Census calculation
            Dan Gordon
            Dan Gordon Sep 21, 2016 1:30 PM (in response to Michael Johnston)

            I have attached a sample of what I am trying to calculate

             

                   

            Census Table
            DATA Example
            Admission DateDischarge Date7/1/20167/2/20167/3/20167/4/20167/5/20167/6/20167/7/20167/8/20167/9/20167/10/2016
            Patient 17/2/20167/9/2016Patient 11111111
            Patient 27/2/20167/7/2016Patient 211111
            Patient 37/3/20167/4/2016Patient 31
            Patient 47/3/20167/8/2016Patient 411111
            Patient 57/4/20167/10/2016Patient 5111111
            Patient 67/4/20167/5/2016Patient 61
            Patient 77/5/20167/10/2016Patient 711111
            Patient 87/5/20167/9/2016Patient 81111
            Patient 97/6/20167/8/2016Patient 911
            Patient 107/9/20167/11/2016Patient 1011
            Total Census 245676431

             

            Trying to calculate the total census for each day, counting the admission day and each day up to the discharge day, but not including the discharge day.

             

            Is this possible?

             

            Thanks,

            Dan

            • 3. Re: Question about Census calculation
              Chris Dickson
              Chris Dickson Sep 21, 2016 2:25 PM (in response to Dan Gordon)

              Hi Dan,

               

              i have created three options for you the first requires a bit of SQL first, this statement will create a date range and then check each day patient by patient if they were in hospital.

               

              1)

              Declare @todate datetime, @fromdate datetime

              Select @fromdate='2016-07-02', @todate='2016-07-11'

               

              ;With DateSequence( Date ) as

              (

                  Select @fromdate as Date

                      union all

                  Select dateadd(day, 1, Date)

                      from DateSequence

                      where Date < @todate

              )

               

              --select result

              Select

              Date,

              PatientId

               

              from DateSequence as D

               

              left join

              Patients as P

              on

              1. P.AdmissionDate<=D.Date

              And

              1. P.DischargeDate>=Date

               

              2)

              The second option is in case you only need to look at one day at a time, you can achieve this using a parameter.

               

              Both options outputs are shown in the attached workbook.

               

              3)

              However from your first post, if you are simply wanting number of patient in a ward on a specific day we would create a dataset with 3 columns Date, Admissions and Discharges, in tableau we calculate net change as Admissions-Discharges, then use a table calculation that does a running sum of net change.

              1 of 1 people found this helpful
              • 4. Re: Question about Census calculation
                Dan Gordon
                Dan Gordon Sep 22, 2016 7:52 AM (in response to Chris Dickson)

                Chris,

                 

                I am experimenting with all three of these options.  Thank you.  Quick question however.  My data goes back to 7/1/2013.  If I am using a filter and only looking at 9/1/16 thru 9/20/16 for example, does the 'Starting Census' parameter and the Running Sum of Net Change still calculate from the first date (7/1/2013) or does it pick up the first date of the filter (9/1/2016)?  Actually, how does filtering on other dimensions or measures affect the Running Sum Net Charge calc?  If I filter on another data field to narrow the target population, will the calculation only calc on the filtered results?

                 

                Thanks,

                 

                Dan