Overlapping dates

[Melodie Suvajac]

Hi, Could you help with identifying which dates in this table have an overlap A flag or any identifier would be helpful. #DrSpotfire

 

 

 

 

ID

Category

Start Date

End Date

 

 

1

A

3/13/22

3/25/22

 

 

2

AE

2/1/22

2/2/22

 

 

3

H

1/4/22

1/5/22

 

 

4

H

1/2/22

1/6/22

 

 

5

H

1/10/22

1/13/22

 

 

6

H

1/9/22

1/14/22

 

 

7

AE

4/5/22

4/7/22

 

 

8

A

3/10/22

3/16/22

 

 

9

A

3/1/22

3/2/22

 

 

10

A

3/15/22

3/17/22

[Fabian Duerr]

You can define an overlapp by checking the condition:

(StartDate

[Gaia Paolini]

I would like to suggest a visual approach, that might not fully answer your question but is quick and easy.

 

1- Take your table and unpivot it:

columns to pass through: ID, Category

columns to transform: Start Date, End Date

category column name: Date Type

category column value: Date Value

This creates a table with columns: ID, Category, Date Type, Date Value.

 

2- Draw a Scatter Plot with:

Date Value on X, ID and Category on Y

Line connection with a separate value for ID

Colour by Category

Shape by Date Type

 

You will get something like the attached image.

In this example, H3 and 4 overlap;H5 and 6 overlap; A1, 8 and 10 overlap.