Within Range: Within Next 1 Month or Within 2 Months

[baliyan_vinay]

Hello Community, 

I have dates greater than today for the next 6 months, I need to implement a filter that will show the range for example,

'Within 2 months': this should select all dates within the next two months, somewhat like a date<=add_months(now(),2) .. 

My filter should list values as

within 1 month(s)

within 2 month(s)

...

within 6 month(s)

Please can you help out here

Thanks

@sunny_talwar 

[sunny_talwar]

Try implementing The As-Of Table

[baliyan_vinay]

With As of table I will get the rolling months, for example, if I have selected Mar-2020 for 2020, then I would have data for Jan, Feb and Mar. This is clear to me. 

What I want is for example, 

date

15-Jun-2020

20-Jun-2020

25-Jun-2020

So within 1 months should return me only 15 and 20 and not 25-Jun as this date is greater than curr_Date plus one month.

[sunny_talwar · Accepted solution]

Checkout a script like this

QuartersMap:
Mapping
LOAD RowNo() as Month,
	 'Q' & Ceil (rowno()/3) as Quarter
AutoGenerate (12);

LET varMinDate = 43831;
LET varMaxDate = 44196;

TempCalendar:
LOAD $(varMinDate) + Iterno()-1 As Num,
	 Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1
While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
LOAD TempDate AS OrderDate,
	 week(TempDate) As Week,
	 Year(TempDate) As Year,
	 Month(TempDate) As Month,
	 Day(TempDate) As Day,
	 YeartoDate(TempDate)*-1 as CurYTDFlag,
	 YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
	 inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
	 date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
	 ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
	 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
	 WeekDay(TempDate) as WeekDay	 
Resident TempCalendar
Order By TempDate ASC;

FilterTable:
CrossTable (Filter, Flag)
LOAD OrderDate,
	 If(OrderDate >= Today(1) and OrderDate <= AddMonths(Today(1), 1), 1) as [Within 1 month],
	 If(OrderDate >= Today(1) and OrderDate <= AddMonths(Today(1), 2), 1) as [Within 2 months],
	 If(OrderDate >= Today(1) and OrderDate <= AddMonths(Today(1), 3), 1) as [Within 3 months],
	 If(OrderDate >= Today(1) and OrderDate <= AddMonths(Today(1), 4), 1) as [Within 4 months],
	 If(OrderDate >= Today(1) and OrderDate <= AddMonths(Today(1), 5), 1) as [Within 5 months],
	 If(OrderDate >= Today(1) and OrderDate <= AddMonths(Today(1), 6), 1) as [Within 6 months]
Resident MasterCalendar
Where OrderDate >= Today(1) and OrderDate <= AddMonths(Today(1), 6);

DROP Table TempCalendar;