Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ANV
Contributor
Contributor

Need Help on How to handle two dates filter conditions

Dear Friends,

I have requirement in which there are two dates (for simplicity Date1 and Date2). Requirement is to have a value per month to be aggregated sum of 4 prior months considering both Date1 and Date 2

For example, On Dashboard when users select a Date1 = Dec'23 then they should get a value for Dec 23 to be sum of previous 4 months i.e., {Aug'23 to Nov'23} where both Date1 and Date2 coincide. for example here in this scenario we should get sum of values from Date2 to be oct'23 and nov'23 = 37

Date1 Date2 Value
Apr-23 Jun-23 1
May-23 Jul-23 6
Jun-23 Aug-23 19
Jul-23 Sep-23 15
Aug-23 Oct-23 18
Sep-23 Nov-23 19
Oct-23 Dec-23 7
Nov-23 Jan-24 2
Dec-23 Feb-24 5

 

Kindly help on this dear friends!

Labels (4)
1 Solution

Accepted Solutions
Dataintellinalytics

Try using Intersection in Set Analysis. Select MonthYearDate1 = Dec'23

Sum({<MonthYearDate1=, Date1 = {"$(='>='&MonthStart(Date1,-4)&'<='&MonthEnd(Date1,-1))"} > *
<MonthYearDate1=, Date2 = {"$(='>='&MonthStart(Date1,-4)&'<='&MonthEnd(Date1,-1))"} > } Value)

Dataset used:

Data:
Load * ,Monthname(Date1) as MonthYearDate1,Monthname(Date2) as MonthYearDate2 ;

Load * Inline [
Date1,Date2,Value
01/04/23,01/06/23,1
01/05/23,01/07/23,6
01/06/23,01/08/23,19
01/07/23,01/09/23,15
01/08/23,01/10/23,18
01/09/23,01/11/23,19
01/10/23,01/12/23,7
01/11/23,01/01/24,2
01/12/23,01/02/24,5
];

 

 

View solution in original post

2 Replies
Dataintellinalytics

Try using Intersection in Set Analysis. Select MonthYearDate1 = Dec'23

Sum({<MonthYearDate1=, Date1 = {"$(='>='&MonthStart(Date1,-4)&'<='&MonthEnd(Date1,-1))"} > *
<MonthYearDate1=, Date2 = {"$(='>='&MonthStart(Date1,-4)&'<='&MonthEnd(Date1,-1))"} > } Value)

Dataset used:

Data:
Load * ,Monthname(Date1) as MonthYearDate1,Monthname(Date2) as MonthYearDate2 ;

Load * Inline [
Date1,Date2,Value
01/04/23,01/06/23,1
01/05/23,01/07/23,6
01/06/23,01/08/23,19
01/07/23,01/09/23,15
01/08/23,01/10/23,18
01/09/23,01/11/23,19
01/10/23,01/12/23,7
01/11/23,01/01/24,2
01/12/23,01/02/24,5
];

 

 

ANV
Contributor
Contributor
Author

Thank you so much for responding to my query. It worked 🙂