Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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)

• ### Set Analysis

1 Solution

Accepted Solutions
Creator

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 ;

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
];

2 Replies
Creator

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 ;

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
];

Contributor
Author

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