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

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

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

Admission Date

Discharge Date

Meanwhile you can try something like this

DATEDIFF('day',[Admission Date],[Discharge Date])

Mahfooj

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

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:

[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

    ELSE

        Null

    END

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

//the number of days in the month

ELSE

    DAY(DATEADD('month', 1, [Month]) -1)

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.

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

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.