-
1. Re: Question about Census calculation
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 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 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
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
- P.AdmissionDate<=D.Date
And
- 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.
-
Census Example.twbx 260.2 KB
-
4. Re: Question about Census calculation
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