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.
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
I have tried with table calculation to split date difference count to all date part(month) values but it is not being achieved.
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.
Meanwhile you can try something like this
DATEDIFF('day',[Admission Date],[Discharge Date])
DATEDIFF('day',[Admission Date],[Discharge Date])
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)
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
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.
Sample v9.0.twbx 9.8 KB
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 -
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?
If you have taken date diff we get total count, and the same count should spread across the months.
2 of 2 people found this helpful
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:
[Month] >= DATETRUNC('month',[Admission Date])
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
DAY(DATEADD('month', 1, [Month]) -1) - DAY([Admission Date]) + 1
//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
//this covers the case where the stay crosses multiple months, just count up
//the number of days in the month
DAY(DATEADD('month', 1, [Month]) -1)
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!
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.
Thanks for the detailed answers
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.
Dummy data workbook.twbx 3.3 MB
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.
Day and Hour.xlsx 65.0 KB
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.