topic Create filter drop down for rolling 3 months, 6 months, 12 months and 24 months in qliksense in App Development

Create filter drop down for rolling 3 months, 6 months, 12 months and 24 months in qliksense

Aspiring_Developer — Fri, 15 Jul 2022 13:15:08 GMT

Hello All,

I wish to create a filter dropdown in front end comprising values - last 3 months, last 6 months, last 12 months, last 24 months.

I ma trying to achieve it via master calendar rather than creating inline table and setting if conditions for chart expressions.

Here is my master calendar script;

Temp:
LOAD
Min(Date) as Mindate,
Max(Date) as Maxdate
Resident Link;

LET vmindate= Num(Peek('Mindate',0,'Temp'));
LET vmaxdate= Num(Peek('Maxdate',0,'Temp'));

DROP Table Temp;

Calendar:
LOAD
$(vmindate) + IterNo() -1 as Num,
Date($(vmindate) + IterNo() -1) as New_Date
AutoGenerate 1
While
($(vmindate) + IterNo() -1)<= $(vmaxdate);

Master_Cal:
LOAD
Floor(New_Date) as Date,
Year(New_Date) as Year,
Month(New_Date) as Month,
MonthName(New_Date) as MonthName,
Day(New_Date) as Day,
YearToDate(New_Date) as YTD,
Week(New_Date) as Week
Resident Calendar;

DROP Table Calendar;

Can anyone please help me how can I create it in the backend master calendar script ?

I am thinking of creating a seperate table, create dimension at backend and then perform cross join with master calendar.

But not able to practically execute it . Please suggest .

@sunny_talwar @kwebbmvp @Brun0 @hic

Re: Create filter drop down for rolling 3 months, 6 months, 12 months and 24 months in qliksense

hic — Fri, 15 Jul 2022 14:13:05 GMT

Calendar:
Load // ------------ generate additional fields ------------
Year(Date) as Year,
Month(Date) as Month,
MonthName(Date) as MonthName,
Day(Date) as Day,
YearToDate(Date) as YTD,
Week(Date) as Week,
If(AddMonths(Date,03)>=Today(),1,0) as IsWithinLast03Months,
If(AddMonths(Date,06)>=Today(),1,0) as IsWithinLast06Months,
If(AddMonths(Date,12)>=Today(),1,0) as IsWithinLast12Months,
If(AddMonths(Date,24)>=Today(),1,0) as IsWithinLast24Months,
Round(12*(Today()-Date)/365.25) as MonthsAgo,
*;
Load // ------------ generate all dates between smallest and largest date ------------
Date( CalendarBegin+Iterno()-1 ) as Date
While CalendarBegin+Iterno()-1<=CalendarEnd;
Load // ------ find smallest and largest value in symbol table; extend to full years ------
YearStart(Min(Fieldvalue('Date',RecNo()))) as CalendarBegin,
YearEnd( Max(Fieldvalue('Date',RecNo()))) as CalendarEnd
Autogenerate FieldValueCount('Date');

TimeClasses:
Load Date, 'Last 3 months' as DateClass
Resident Calendar
Where Today()>=Date and MonthsAgo<3;
Load Date, 'Last 6 months' as DateClass
Resident Calendar
Where Today()>=Date and MonthsAgo<6;
Load Date, 'Last 12 months' as DateClass
Resident Calendar
Where Today()>=Date and MonthsAgo<12;
Load Date, 'Last 24 months' as DateClass
Resident Calendar
Where Today()>=Date and MonthsAgo<24;

Re: Create filter drop down for rolling 3 months, 6 months, 12 months and 24 months in qliksense

rwunderlich — Fri, 15 Jul 2022 15:15:02 GMT

Here's another approach that creates a range table linked to the Date field in the master Calendar. This script is from https://qlikviewcookbook.com/recipes/download-info/date-grouping/

/* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week".
Date table was created on the previous tab.
*/
// Assign current date to a variable. Makes INLINE below a little neater .

LET vToday=today(1);

Ranges:
// Use the "evaluate()" function to execute the input function text and convert it to actual date values.
LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd
;
/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.
The Start/End values are defined using standard QV date functions.
Semicolon is used for a field delimeter because some of the functions contain commas.
*/
LOAD * INLINE [
Range; RangeStart; RangeEnd
Today; vToday; DayEnd(vToday)
Tomorrow; vToday+1; DayEnd(vToday+1)
Next Workday; LastWorkDate(vToday, 2); DayEnd(LastWorkDate(vToday, 2))
Yesterday; vToday-1; DayEnd(vToday-1)
This Week; WeekStart(vToday); WeekEnd(vToday)
Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)
Last 2 Weeks; WeekStart(vToday,-1); WeekEnd(today(1))
This Month; MonthStart(vToday); MonthEnd(vToday)
Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)
Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)
This Year; YearStart(vToday); YearEnd(vToday)
Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)
] (delimiter is ';')
;

/*
Use IntervalMatch to link the Date field into multiple Range fields.
*/
JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;
// Because we did a JOIN, we may drop the the Start/End fields.
DROP FIELDS RangeStart, RangeEnd;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com