Need help finding records with overlapping date ranges

[Former Member]

Hello,

I have a dataset that looks like this:

I need to be able to highlight rows where the same person has overlapping dates, (which are an error.)

So, in this case, these rows would be highlighted or otherwise indicated:

any assistance is much appreciated.

[amitrathi239 · Accepted solution]

follow these steps.

Create variable min1=Min([Start date]) In ([Person ID])

min2=Min([End date]) In ([Person ID])

max1=Max([Start date]) In ([Person ID])

Final Count variable=Count([Response ID]) In ([Person ID]) Where ([max1] Between ([min1];[min2]))

[Former Member]

Thanks to both of you! Amit's did what i need!

[JBARLOW]

A possible workaround solution - although far from ideal as it relies on sorting the data (any changes to the sort will mess it up)

1. Using the daynumberofyear Function create two report variables Start & End e.g. =DayNumberOfYear([Start Date])

2. Use this formula in the last column:=If([START]<Previous([END];([PERSON ID]));1;0)

3. Sort the data in the table as shown below

4. I just used conditional formatting to highlight the flagged row.

[JBARLOW]

@ Amit,
I think the question is not so much counting duplicate Person ID's but needing to count if there are multiple Response ID's for a given Person ID that contain an overlapping date - in the example P103 has two responses but they are in different date ranges.
Whereas P104 has a date overlap on 10,11,12th Jan ..... tricky one to solve

[amitrathi239]

Former Member thanks for pointing out.check my solution posted now.

[amitrathi239]

follow these steps.

Create V Count variable=Count([Response ID]) In ([Person ID])

Create new rule on V Count variable and add the rule value according to the screenshot.Same apply on all columns.