Including specific dates based on a criteria in Set Analysis

SiDB — Fri, 16 Apr 2021 12:10:38 GMT

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.

Re: Including specific dates based on a criteria in Set Analysis

SiDB — Sat, 24 Apr 2021 12:39:22 GMT

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

topic Re: Including specific dates based on a criteria in Set Analysis in QlikView

Including specific dates based on a criteria in Set Analysis

Re: Including specific dates based on a criteria in Set Analysis