13 Replies Latest reply on Aug 17, 2017 9:20 AM by Jonathan Drummey

# Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Folks,

Here is one challenge I come up with.

Admission Date Discharge Date August September October Date Column 8/31/2016 9/1/2016 1 0 0 8/30/2016 9/5/2016 2 4 0 8/12/2016 8/12/2016 1 0 0 8/12/2016 10/25/2016 20 30 24 5/15/2016 11/30/2016 31 30 31 8/12/2016 8/13/2016 1 0 0

So here in the above data, August, September and October months are the date parts of Date column and the number in these 3 columns are the counts for each date parts for 'Date Column'

Note: August, September, October are the date parts of one date column.

Regards,

Deepak

Message was edited by: Deepak G S I have attached the xlsx again.

• ###### 1. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Deepak,

It seems doable in tableau however we need some info. Can you explain which date column you're referring from your given data set?

Mahfooj

• ###### 2. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Mahfooj,

The respective column here is a Data Column and it consists of 3 months values - August, September and October.

And August, September and October are not the 3 different columns. It is just the date part.

Attached is the screen shot.

Required:

Admission Date = 9/6/2016 and Discharge Date is 10/11/2016 then my Date column should contain like below

August     September     October

0               24                    11

Please let me know if you have any work around to achieve this.

I have tried with table calculation to split date difference count to all date part(month) values but it is not being achieved.

Regards,

Deepak

• ###### 3. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

I have created as excel formula for same. You can convert another derived dimension from date field and in formula instead of using 8, 9, 10 you can used month[derived dimension].

Here it is...

Do let me know if this helps.

Regards

Sandeep

• ###### 4. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Deepak,

Could you please provide the sample data for below mentioned fields? You don't need to send original data create a dummy set and share for one or two cases with your expected results.

MOS

Case Number

Discharge Date

Meanwhile you can try something like this

Mahfooj

• ###### 5. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Mahfooj,

The above will give us the difference in days - but I have to distribute the same count to August, September, October and so on,

I have attached an excel, you shall use the same to get the exact number. In the spread sheet I have marked in yellow (expected number)

Regards,

Deepak

• ###### 6. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Deepak,

Sorry to say I couldn't find any attached excel. However, I don't know how much I've understood your requirement. Though this is what I've assumed you're looking for. Find my approach below.

I've created a dummy data which is very similar to your screenshot.

After connecting in Tableau

So, as per my understanding you wants number of days for a case on month basis. Correct me If I'm wrong.

Usign simple DATEDIFF() you can get the number of days between Admission and Discharge Date.

Formula would be: DATEDIFF('day',[Admission Date],[Discharge Date])

If you drag this calculated field in canvas your view will be like this

But your requirement is something like this. You wants the null should be display with 0

For that you need to create a calculated field using LOOKUP() and use in your view like this

ZN(LOOKUP(SUM([Date Difference]),0))

While using table function you need to set the data partitioning and addressing like this

Once done you can get the out

Sample workbook v9.0 attached for your reference. Let us know if this works for you.

Mahfooj

• ###### 7. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Mahfooj,

I have attached the xlsx in the original post.

If you see, in your output the count 41 should spread across other months.

I would like to give example again -

Example:

If patient admission date and discharge date is 79 - Count

And the patient has admitted in August - 08/12/2016 and will get discharge in 09/21/2016.

Then out put must be

August            September                 October

29                    30                              20

So basically the count of bed day should spread across the other months.

I hope my requirement is clear to you now?

Regards,

Deepak

• ###### 8. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

If you have taken date diff we get total count, and the same count should spread across the months.

• ###### 9. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Jonathan Drummey Would like to tag you.

Any input Jonathan on this problem..

• ###### 10. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Deepak,

Just to be clear, the Excel data that you posted looks like this:

I'm guessing that the data that you are working with really looks more like this, i.e. doesn't have those extra columns. Is that correct?

If so then that is creating some confusion among the other answerers, in the future it would be better to be absolutely clear about what your raw data is and what calculations you have derived from it.

Assuming that I'm correct in my guess, now we can get more clear on the issue here.

This is a common situation in healthcare where we have a record for each patient encounter, visit, admission, etc. with a start/admission/arrival date and an end/discharge/departure date and what we want to do is ask the question how many patients were present at A,B,C times?

Or, to put this another way, the data is at the grain of individual encounters, but we want to work with the data at an even *finer* grain of the encounter and each month/day/hour/minute that the encounter was taking place, or in this particular case days in month. So instead of an encounter being a single record in one way we want to count that encounter as 1-N records where the number of records is the number of months the patient was in-house.

At a very high level there are two ways to do this:

1) We can create multiple calculations in Tableau, one for each month we need to count, then build out a Measure Names/Values table. This is essentially what you were doing in Excel. The challenge with this is that things like grand totals, overall averages, running totals, % difference, etc. get a lot harder to compute in Tableau.

2) The other method is to pad out the data so that the grain of the data really is one record for each patient & each month they are present. There are many ways to do this, there's a whole list at http://community.tableau.com/message/191608.

With Tableau v10.0 and higher there are some new ways we can do the padding by taking advantage of Tableau's cross-database joins, and in Tableau v10.2 we have join calculations to make it even easier. Here's a view build in v10.2:

Here's the data source:

The join itself uses join calculations of 1 on each side to generate a cross product of all cases & all months. This is then reduced to just 1 case per month with the Month Data Source Filter calculated field that has the formula:

AND [Month] <= DATETRUNC('month',[Discharge Date])

This is used as a data source filter, filtering for True.

Then to build the days in month calculation the formula is this:

//uses counting midnights logic the discharge date is effectively not counted, with a minimum of 1 day

//for a same day admit & discharge

//also imputes a Null discharge date to be TODAY()

//admit date & discharge date are in same month

IF DATETRUNC('month', [Admission Date]) = DATETRUNC('month', IFNULL([Discharge Date],TODAY())) THEN

MAX(DATEDIFF('day', [Admission Date], IFNULL([Discharge Date],TODAY())), 1)

//count days for the month of admin, with a minimum of 1 day

ELSEIF [Month] = DATETRUNC('month', [Admission Date]) THEN

//for the discharge date we ignore the discharge date, so if the discharge date is

//on the first then return Null

ELSEIF [Month] = DATETRUNC('month', IFNULL([Discharge Date],TODAY())) THEN

IF DAY(IFNULL([Discharge Date],TODAY())) != 1 THEN

DAY(IFNULL([Discharge Date],TODAY())) - 1

ELSE

Null

END

//this covers the case where the stay crosses multiple months, just count up

//the number of days in the month

ELSE

END

Then we can use that as a measure with Months as a dimension to build the workout view above, and we don't need Case number in the view to do other calculations like this where i used a quick table calculation to get the difference in days in month.

v10.2 workbook is attached. Let me know if you have any questions!

Jonathan

PS: I could have avoided the complexity of the months calculation by doing the cross product on days instead of months, however that would result in a data source that has one row per patient & day vs. one row per patient & month, i.e. ~15x larger.

2 of 2 people found this helpful
• ###### 11. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Jonathan,

Yes, the excel data which you have shown is correct. Only 3 column - Admission date, discharge date and Case number. The other 3 column which I have highlighted with yellow color is the expected results. (dummy data)

So, using the 2nd method you mentioned - how did we get the secondary data source?

Do we need to manually put the values of month/year into one new excel file and have the cross database join?

My data is coming from the data base and not the excel, in that case do I need to load the dummy secondary data source which has initial month/year value till the end date?

Attached is my work book, would you please take a look once?

I completely understood the way you worked on this requirement, but I do have challenges to achieve this as I mentioned above.

Regards,

Deepak

• ###### 12. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

Hi Deepak,

The additional data source is an Excel file that I made by entering the first date and then doing a fill down, it only takes a few seconds to build. The fact that it's an Excel file doesn't really have any effect, since we're using Tableau v10+ we can do cross-database joins from most any database to Excel spreadsheets and vice versa. I'm attaching the file I used to this post.

Since your patient data is coming from a database there are a couple of options:

1) Use the Excel file & connect to your database and follow the rest of what I did above.

2) Create a table of months in your database (or potentially via Custom SQL or a custom view) and join on that. This would be faster than the cross-database join, and depending on your database you might not even need the data source filter because you could set up a non-equijoin. This is also possible to do in Custom SQL or a custom view.

Jonathan

• ###### 13. Re: Number of days between Admission date and Discharge - Distribute the count to different month stay at hospital

One more thing - the workbook you posted was using v10.4 which is still in beta, in the future please stick to using already released versions of Tableau to create workbooks for the forums since not everyone has the beta.