Qlik Custom Calendars (for Qlik Sense & QlikView)

Qlik Sense and QlikView both handle dates via list boxes (or Filter Panes in Sense). The conventional approach to providing users with date selectors is to create a master calendar and create list boxes in the application to select things such as “Year”, “Month” and “Quarter”. This works well enough, and it addresses issues with missing dates in the data. However, in many cases a more friendly and ergonomic date selector is desirable. Custom Calendars in Qlik load scripts can provide a better way for users to select date ranges. A custom calendar can allow users to select things such as “This Month” or “Last Week” or “Last Two Years”. Such scripting can be combined with a Master Calendar to ensure there are gaps in the date ranges. The following script creates a custom calendar in Qlik Sense or Qlik View load scripts.

You can also download a text file with this script in it here: qlik-custom-calendar

// CUSTOM CALENDAR FOR QLIK LOAD SCRIPT
// ——————————————————————————————
// Original work by John Witherspoon as posted in QlikCommunity.com
// Updated and refined by Tom Meers at Result Data (www.resultdata.com)
// Portions (c) 2010-2016 Result Data
// Note: Replace the [start_date] field on the INTERVAL MATCH line with a date field from the data model

LET v_today = today(1);

//— NEW TABLE NAME
“CAL”:

//— PRECEDING LOAD: table with start and end date range fields
LOAD [Date Range], date(evaluate(range_start)) as range_start, date(evaluate(range_end)) as range_end;

//— DATA LOAD: As an inline (hard coded) table using the v_today variable to populate start and end range fields
LOAD * INLINE [
“Date Range”; range_start; range_end

This Week; WeekStart(v_today); WeekEnd(v_today)
Next Week; WeekStart(v_today,+1); WeekEnd(v_today, +1)
Next 2 Weeks; WeekStart(v_today,+1); WeekEnd(v_today,+2)
This Month; MonthStart(v_today); MonthEnd(v_today)
This Year; YearStart(v_today); YearEnd(v_today)

Last Week; WeekStart(v_today,-1); WeekEnd(v_today, -1)
Last 2 Weeks; WeekStart(v_today,-1); WeekEnd(v_today)
Last Month; MonthStart(v_today,-1); MonthEnd(v_today,-1)
Last Two Months; MonthStart(v_today,-1); MonthEnd(v_today)
Last Year; YearStart(v_today,-1); YearEnd(v_today,-1)

Today; v_today; DayEnd(v_today)

] (delimiter is ‘;’);

//— JOIN THE NEW TABLE TO MODEL: Use an interval match for the date range
//— Note: Replace the [start_date] field below with a date field from the data model
JOIN (“CAL”)
IntervalMatch ([start_date])
LOAD range_start, range_end RESIDENT “CAL”;

//— DROP UNNECESSARY FIELDS: Don’t need start and end date fields, get rid of them
DROP FIELDS range_start, range_end;

 

Comments are closed.