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

# Healthcare - how to filter/calculate by date range

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

Hi Amanda

Can you attach workbook.

thanks

sankar

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

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

Hi,

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

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

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

Thank you so much!

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

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 Date Discharge Date Patient 1 7/2/2016 7/9/2016 Patient 2 7/2/2016 7/7/2016 Patient 3 7/3/2016 7/4/2016 Patient 4 7/3/2016 7/8/2016 Patient 5 7/4/2016 7/10/2016 Patient 6 7/4/2016 7/5/2016 Patient 7 7/5/2016 7/10/2016 Patient 8 7/5/2016 7/9/2016 Patient 9 7/6/2016 7/8/2016 Patient 10 7/9/2016 7/11/2016

What I am trying to do is something like this:

 7/1/2016 7/2/2016 7/3/2016 7/4/2016 7/5/2016 7/6/2016 7/7/2016 7/8/2016 7/9/2016 7/10/2016 Patient 1 1 1 1 1 1 1 1 Patient 2 1 1 1 1 1 Patient 3 1 Patient 4 1 1 1 1 1 Patient 5 1 1 1 1 1 1 Patient 6 1 Patient 7 1 1 1 1 1 Patient 8 1 1 1 1 Patient 9 1 1 Patient 10 1 1 Total Census 2 4 5 6 7 6 4 3 1

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

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