Skip to Content
author's profile photo Former Member
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 a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    Posted on Feb 05, 2018 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]))


    untitled.png (31.8 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 05, 2018 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 a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 05, 2018 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.


    date-range-1.png (26.5 kB)
    date-range-2.png (38.6 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 19, 2018 at 06:44 AM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 05, 2018 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)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.