8 Replies Latest reply on Sep 22, 2016 8:41 AM by Rahul Upadhye

    Healthcare - how to filter/calculate by date range

    Amanda Lubit

      I have a data set of patients that contains the quarter that patient was seen, their start date (which is often prior to the quarter), and their date of exit from the program (which only appears if the date falls in the quarter).

       

      I am trying to visualize the total number of clients seen each quarter (this one is easy), the total number who started that quarter, and the total number who exited (also easy since if they have an exit date then we know they exited. To further complicate this, I'm looking at multiple quarters side by side.

       

      I can't figure out how to get the number of clients who started that quarter. Can anyone tell me how to create a calculation (or something more appropriate) that will generate all clients who have a a start date within the quarter (eg. between 7/1/14-9/31/14 = Q1, 10/1/14-12/31/14 = Q2...), and that can be on the same visualization with those other calculations?

       

      Thanks!
      Amanda

        • 1. Re: Healthcare - how to filter/calculate by date range
          Sankarmagesh Rajan

          Hi Amanda

           

          Can you attach workbook.

           

          thanks

          sankar

          • 2. Re: Healthcare - how to filter/calculate by date range
            Amanda Lubit

            Because of healthcare information I have to send you a PDF rather than the real workbook, but this is the visualization I'm trying to create. To be able to show you what I want, I cheated and did it through a quick excel sort. If you look at the excel sheet I sent what I'm trying to do is have tableau look at the Start Date and calculate does it fall within the quarter which is listed in column 1. Once I get a yes/no on that, I also need to sort it again based on whether the episode was mental health or substance abuse.

             

            Does that help explain it at all?

            Thanks!

            1 of 1 people found this helpful
            • 3. Re: Healthcare - how to filter/calculate by date range
              Rahul Upadhye

              Hi,

              Your data had a missing field (Patient Name). So I added this column into your excel file.

              Since you have date values in your data (start date, end date), you don't need to additionally create the Quarter column.

              Tableau can play around with your date value to show it as Years, Quarters, Months, Weeks, Dates.

              Here is a mockup for your data. Let me know if you have more specific requirement.

              1 of 1 people found this helpful
              • 4. Re: Healthcare - how to filter/calculate by date range
                Amanda Lubit

                I actually couldn't seem to download the workbook.

                 

                And I do need the quarter in there like that because I use this data for a lot of other calculations. The quarter column me who received services that quarter. The start date is often outside the quarter, and many clients have no end date because they are still active clients.

                 

                What I'm envisioning is a formula/calculations that does these steps:

                1st - needs to assign the start date to a quarter

                If [start date] is between july and september, then return [Quarter 1]

                 

                2nd - needs to assign the start date to a year

                if [start date] is within 2014 then return [2014]

                 

                3rd - To create the graphic I want to use whatever calculation to then visualize the # clients who had intakes each quarter

                1 of 1 people found this helpful
                • 5. Re: Healthcare - how to filter/calculate by date range
                  Rahul Upadhye

                  Hi, You should be able to see those attachments in Excel & Tableau.

                  Your requirements are pretty easy & can be done with a function datetrunc()

                  1st- datetrunc('quarter', [Start Date])

                  2nd- datetrunc('year', [Start Date])

                  3rd- pull out those calculated fields in the view & you should be able to see the results.

                  1 of 1 people found this helpful
                  • 6. Re: Healthcare - how to filter/calculate by date range
                    Amanda Lubit

                    Thank you so much!

                    1 of 1 people found this helpful
                    • 7. Re: Healthcare - how to filter/calculate by date range
                      Dan Gordon

                      Hello All,

                       

                      I have a similar problem.  I am trying to count the number of patients that here on any given day.  I have the patients arrival date/hour and their discharge datr/hour..  Lets say the Admission Date is 7/1/16 and the discharge date is 7/9/12.  How do I return a value of 1 for this patient on 7/2, 7/3, 7/4, 7/5, 7/6, 7/7 and 7/8 (Do not count the day of discharge)?

                       

                      Here is an example of an abbreviated data set:

                         

                      DATA Example
                      Admission DateDischarge Date
                      Patient 17/2/20167/9/2016
                      Patient 27/2/20167/7/2016
                      Patient 37/3/20167/4/2016
                      Patient 47/3/20167/8/2016
                      Patient 57/4/20167/10/2016
                      Patient 67/4/20167/5/2016
                      Patient 77/5/20167/10/2016
                      Patient 87/5/20167/9/2016
                      Patient 97/6/20167/8/2016
                      Patient 107/9/20167/11/2016

                       

                      What I am trying to do is something like this:

                         

                      7/1/20167/2/20167/3/20167/4/20167/5/20167/6/20167/7/20167/8/20167/9/20167/10/2016
                      Patient 11111111
                      Patient 211111
                      Patient 31
                      Patient 411111
                      Patient 5111111
                      Patient 61
                      Patient 711111
                      Patient 81111
                      Patient 911
                      Patient 1011
                      Total Census 245676431

                       

                      There are thousands of patient records in my actual data set.  My Admission and Discharge dates are in Days and hours.  I don't really want to create a table like what is above, all I am trying to accomplish is to determine the total Patients on any given day as of 11:59PM.

                       

                      Is this something that can be calculated?

                       

                      I am new to Tableau and appreciate anyone's assistance.

                       

                      Dan Gordon

                      Frederick Memorial Hospital

                      DGordon@fmh.org

                      1 of 1 people found this helpful
                      • 8. Re: Healthcare - how to filter/calculate by date range
                        Rahul Upadhye

                        Hey Dan,

                         

                        Based on your current data model it may be (very) challenging to achieve the expected result.

                        If you are allowed to change the data model, i can show how to make your data "Tableau Friendly".

                        1 of 1 people found this helpful