12 Replies Latest reply on Jan 14, 2019 10:04 AM by Adam Ackerman

# Determining the number of days (gaps) between multiple date ranges

I am working with data that includes start and end dates for patients that are considered eligible for certain medical coverage.  Some patients have one range of dates while others have multiple ranges (multiple start and end dates) across multiple years.  My quest is to understand how to visualize the gaps between date ranges.

Example:

Patient ID#1234

start date:  2/1/2014

end date:  11/15/2014

--------------------------

start date: 12/20/2014

end date:  5/4/2015

---------------------------

start date: 5/5/2015

end date: 9/30/2015

There are 3 distinct date ranges of medical eligibility for this person.  I want to quantify the in-between gaps and be able to show these gaps on a timeline or perhaps a Gantt graph. Essentially see over time when the person was covered and when they weren't.

I do not have any sample data, unfortunately.  But any ideas of examples would be awesome.  Thanks.

• ###### 1. Re: Determining the number of days (gaps) between multiple date ranges

Yes, a Gantt chart would be perfect here.

Looking at your data, it's pretty straightforward to create a Gantt chart out of that.

Check out these links here:

http://kb.tableau.com/articles/knowledgebase/gantt-chart?lang=en-us

Hope this helps.

Cheers,

Seinchyi

• ###### 2. Re: Determining the number of days (gaps) between multiple date ranges

Hi Seinchyi,

This helps a little.  My main struggle is quantifying the gap (in days) between one end date and the next start date.  A person will be within a date range (call this time "covered") and then spend time outside any covered range (call this time "uncovered").  I'd like to count the number of gap periods, the total gap days, and also get an average # of days for a particular person.  In looking at my example above, the person has two gaps in coverage and the total number of uncovered days is 36 (an average of 18 days per gap in coverage).  How can I automate this calculation across a data set of thousands of people?

Thanks!

• ###### 3. Re: Determining the number of days (gaps) between multiple date ranges

I believe this can be done with a table calculation and hope those who know how share good solutions.

A correlated subquery is a way to keep the extra field in the same row in underlying data:

```SELECT [Patient ID]
, ( select max([end date])
from [data\$] d2
where d2.[Patient ID] = d.[Patient ID]
and d2.[start date] < d.[start date]
) as [previous end date]
, [start date]
, [end date]
FROM [data\$] d

```

Attached example uses Excel. One needs to open it with the legacy connector for getting access to custom SQL:

It would be nice if Tableau supported correlated subqueries and therefore posted Correlated Subquery.

If you also would like this to be better supported, then you can influence Tableau to do so by up-voting mentioned idea.

Your question is also added to a collection of scenarios where a correlated subquery could be valuable.

Attached Workbook Version:  9.0

.

• ###### 4. Re: Determining the number of days (gaps) between multiple date ranges

Many thanks!

I'll play around with this and see how it goes on a bigger scale.  But this seems to be what I need.

And thanks for the advice on up-voting.

• ###### 5. Re: Determining the number of days (gaps) between multiple date ranges

Just so you know, I have updated the attached workbook. The corrections are:

1. sharing the real workbook - first time I forgot to save it before attaching it
• now worksheet is included
• now calculation [days gap] is included
2. corrected the custom SQL - had originally forgot to include [Patient ID] in the join
• ###### 6. Re: Determining the number of days (gaps) between multiple date ranges

Here it is using a table calc:

Previous End Date = LOOKUP(ATTR([End Date]),-1)

Compute using table down.

Regards,

Gerardo

• ###### 7. Re: Determining the number of days (gaps) between multiple date ranges

Here is my solution for visualizing the gap days, which can be made totally automatic. Note how the table is structured. A date shift Date+ is needed to denote the beginning of a new status.

Date range: 607 days

Covered Days: 573 days

Gap days: 34 days

https://public.tableau.com/views/DaysoverMultipleDateRanges/Dashboard1?:embed=y&:display_count=yes&:showTabs=y

The calculation includes a running sum of a running sum. It is based on a technique explained here:

• ###### 8. Re: Determining the number of days (gaps) between multiple date ranges

The only issue I'm have now is having the total 'gap in days' reset for every new patient ID.  The current calcs work great for one patient but in my case, we need to restart the calculation for each new patient ID record.  My data set has tens of thousands of patient IDs.  Some have gaps while others have no gaps.

I've attached an example of 4 patients.  Two of them have no gaps.  One has a single gap while another has two gaps.

Any ideas?

Thx!

• ###### 9. Re: Determining the number of days (gaps) between multiple date ranges

This works for multiple patients and multiple gaps . See this

http://community.tableau.com/message/381432

Will have a look at your case later. Try it yourself if you can.

On Friday, November 6, 2015, Adam Ackerman <

• ###### 10. Re: Determining the number of days (gaps) between multiple date ranges

This is another use case:

Identifying Overlapping Dates

• ###### 11. Re: Determining the number of days (gaps) between multiple date ranges

Adam, have you managed to solve the problem?

I may have found a solution to a similar challenge using table calcs:

I haven't used custom SQLs/merges and I don't have any fields in the data identifying gaps. This is a pure table calc solution. If someone is interested to know I can post a detailed explanation.

• ###### 12. Re: Determining the number of days (gaps) between multiple date ranges

George - I believe this was resolved a while ago.  But thanks for the comments.  If this helps anyone else out there, I'd call it a "win"!