Skip to Content
0

Need help finding records with overlapping date ranges

Feb 05 at 07:00 AM

60

avatar image
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.

adf4m.png (5.6 kB)
pha61.png (5.6 kB)
k9ap8.png (7.0 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
AMIT KUMAR
Feb 05 at 12:25 PM
0

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]))


untitled.png (31.8 kB)
Share
10 |10000 characters needed characters left characters exceeded
AMIT KUMAR
Feb 05 at 10:18 AM
-1

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.


untitled.png (69.3 kB)
Share
10 |10000 characters needed characters left characters exceeded
James Barlow Feb 05 at 11:12 AM
0

@ 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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
James Barlow Feb 05 at 12:24 PM
0

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.


date-range-1.png (26.5 kB)
date-range-2.png (38.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 19 at 06:44 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded