Skip to Content
avatar image
Former Member

Need help finding records with overlapping date ranges

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Feb 05 at 12:25 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • 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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 05 at 11:12 AM

    @ 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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 05 at 12:24 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 19 at 06:44 AM

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

    Add comment
    10|10000 characters needed characters exceeded