topic Re: Within Range: Within Next 1 Month or Within 2 Months in QlikView

Within Range: Within Next 1 Month or Within 2 Months

baliyan_vinay — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Within Range: Within Next 1 Month or Within 2 Months

sunny_talwar — Mon, 18 May 2020 11:22:09 GMT

Try implementing The As-Of Table

Re: Within Range: Within Next 1 Month or Within 2 Months

baliyan_vinay — Wed, 20 May 2020 11:43:26 GMT

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.

Re: Within Range: Within Next 1 Month or Within 2 Months

sunny_talwar — Wed, 20 May 2020 12:15:08 GMT

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;