Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
SiDB
Contributor
Contributor

Including specific dates based on a criteria in Set Analysis

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

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

KEYTYPEDATE
1Opened01/04/2021
1Closed02/04/2021
2Opened01/03/2021
2Sold01/04/2021
3Opened03/04/2021
3Closed06/05/2021
4Opened04/04/2021
4Sold05/04/2021
5Opened02/06/2021
5Closed19/06/2021
6Opened06/04/2021
6Sold23/04/2021
7Opened28/03/2021
7Closed04/04/2021
8Opened16/04/2021
8Sold04/05/2021
9Opened08/03/2021
9Closed06/04/2021
10Opened02/04/2021
10Sold05/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

 

1 Solution

Accepted Solutions
SiDB
Contributor
Contributor
Author

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

View solution in original post

1 Reply
SiDB
Contributor
Contributor
Author

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

View solution in original post