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

    Deepak G S

      Hi Folks,

       

      Here is one challenge I come up with.

       

      Date Column
      Admission DateDischarge Date
      AugustSeptember
      October
      8/31/20169/1/2016100
      8/30/20169/5/2016240
      8/12/20168/12/2016100
      8/12/201610/25/2016203024
      5/15/201611/30/2016313031
      8/12/20168/13/2016100

       

      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
          Mahfooj Khan

          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
            Deepak G S

            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.datesyner.PNG

             

            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
              Sandeep Das

              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
                Mahfooj Khan

                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

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

                  Hi Mahfooj,

                   

                  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)

                   

                  Regards,

                  Deepak

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

                    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
                      Deepak G S

                      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
                        Deepak G S

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

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

                          Hi Deepak,

                           

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

                           

                          Screen Shot 2017-08-17 at 9.37.15 AM.png

                           

                          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?

                           

                          Screen Shot 2017-08-17 at 9.37.27 AM.png

                           

                          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:

                           

                          Screen Shot 2017-08-17 at 10.13.30 AM.png

                           

                          Here's the data source:

                           

                          Screen Shot 2017-08-17 at 10.15.35 AM.png

                           

                          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.

                           

                          Screen Shot 2017-08-17 at 10.19.42 AM.png

                           

                          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
                            Deepak G S

                            Hi Jonathan,

                             

                            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.

                             

                            Regards,

                            Deepak

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

                              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
                                Jonathan Drummey

                                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.