Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
];
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
];
Thank you so much for responding to my query. It worked 🙂