topic Re: Master Calendar Date Flags as Listbox in QlikView

Master Calendar Date Flags as Listbox

Anonymous — Wed, 01 Jun 2016 13:11:36 GMT

Hello all,

I have a rather large table with a date field which need sliced and diced in various ways. To this end I created a calendar table to correspond to the field. Within the calendar I set up flags to track the following date ranges:

Year to Date

Quarter to Date

Month to Date

Last Year

Last Quarter

Last Month

The code for creating the calendar is as follows:

open_date_calendar:
Load
Num AS OPEN_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,
if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,
if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,
if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,
if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,
if(InMonth(TempDate, today(),-1), 1, 0) as LastMonthFlag

Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

The last six fields in the table correspond to the date ranges listed above. What I need is a listbox in the application that lists the six date ranges and filters the underlying data based on the flags. This has been quite difficult as you can imagine as there is no easy way to set up a single list box to filter six different fields.

I've kind of hacked it together using text boxes and a lot of Actions as well as some conditional coloring to make it look like a listbox, but I'm wondering if anyone has any ideas on an easier way. I'm open to restructuring the calendar table in whatever way necessary. My goal is a list box that looks like this:

So if you click "Year to Date" it would select all records where CurYearFlag = 1 or if you were to click "Quarter to Date" it would select all records where CurQtrFlag = 1 etc.

Re: Master Calendar Date Flags as Listbox

swuehl — Wed, 01 Jun 2016 13:57:59 GMT

Maybe try like this:

CROSS:

CROSSTABLE ([Date Ranges], Flag)

LOAD OPEN_DATE, CurYearFlag as [Current Year], CurQtrFlag as [Current Quarter],CurMonthFlag as [Current Month], LastYearFlag as [Last Year], LastQtrFlag as [Last Quarter], LastMonthFlag as [Last Month]

RESIDENT open_date_calendar;

DateRanges:

LOAD OPEN_DATE, [Date Ranges]

RESIDENT CROSS

WHERE Flag =1;

DROP TABLE CROSS;

Re: Master Calendar Date Flags as Listbox

Anonymous — Wed, 01 Jun 2016 14:28:43 GMT

Thank you for the quick reply. Your code works perfectly! I had not seen the crosstable functionality before.