Including specific dates based on a criteria in Set Analysis

[SiDB]

Hi, I'm really stuck here, I hope someone can help me.

I have a table that contains a Key, Date & Type field. 

KEY	TYPE	DATE
1	Opened	01/04/2021
1	Closed	02/04/2021
2	Opened	01/03/2021
2	Sold	01/04/2021
3	Opened	03/04/2021
3	Closed	06/05/2021
4	Opened	04/04/2021
4	Sold	05/04/2021
5	Opened	02/06/2021
5	Closed	19/06/2021
6	Opened	06/04/2021
6	Sold	23/04/2021
7	Opened	28/03/2021
7	Closed	04/04/2021
8	Opened	16/04/2021
8	Sold	04/05/2021
9	Opened	08/03/2021
9	Closed	06/04/2021
10	Opened	02/04/2021
10	Sold	05/04/2021

If I choose a date range of 01/04/2021 to 07/04/2021

I want my Set Analysis to filter my data to count the number of distinct records in two different scenarios.

SCENARIO 1:

COUNT{<Type={"Sold","Closed">}DISTINCT KEY)  where the Date for all 3 types match the Date Range.

The result for this scenario would be 3 (Keys 1,4,10)

SCENARIO 2:

I need the same expression but where the Date range only matches Type=Opened and ignores the Date for the other 2 types.

The result for this scenario would be 5 (Keys 1,3,4,5,10)

Thank you in advance for any help, I'm really struggling with this.

Si

[SiDB · Accepted solution]

I've resolved this now by creating a second date column that only contains the opened date for every row and by creating two calendars for the date columns