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
