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

# Question about Census calculation

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

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

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

 Census Table DATA Example Admission Date Discharge Date 7/1/2016 7/2/2016 7/3/2016 7/4/2016 7/5/2016 7/6/2016 7/7/2016 7/8/2016 7/9/2016 7/10/2016 Patient 1 7/2/2016 7/9/2016 Patient 1 1 1 1 1 1 1 1 Patient 2 7/2/2016 7/7/2016 Patient 2 1 1 1 1 1 Patient 3 7/3/2016 7/4/2016 Patient 3 1 Patient 4 7/3/2016 7/8/2016 Patient 4 1 1 1 1 1 Patient 5 7/4/2016 7/10/2016 Patient 5 1 1 1 1 1 1 Patient 6 7/4/2016 7/5/2016 Patient 6 1 Patient 7 7/5/2016 7/10/2016 Patient 7 1 1 1 1 1 Patient 8 7/5/2016 7/9/2016 Patient 8 1 1 1 1 Patient 9 7/6/2016 7/8/2016 Patient 9 1 1 Patient 10 7/9/2016 7/11/2016 Patient 10 1 1 Total Census 2 4 5 6 7 6 4 3 1

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

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

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