Sunday, 2 October 2016

Making Awesome Qlik Sense Date Dropdowns

One of my favourite things in Qlik Sense is the new calendar function, however talking with other developers I've found that some people don't know about them yet.

Here's how to get that lovely drop down expanding from all your date fields that you see in all the Qlik demoes...




I think the issue that developers are facing is that they're coding Qlik scripts directly, missing some of the best functions that Qlik are implementing for the ease of customers without any knowledge of writing script.

I've taken the auto-generated script and expanded it to give me more features, everything below the green is me, everything above is Qlik.

//AutoCalendar
[Calendar]: 
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1))  AS [Year] Tagged ('$axis', '$year')
  ,Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter')
  ,Month($1) AS [Month] Tagged ('$month')
  ,Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth')
  ,Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber')
  ,Date(Floor($1)) AS [Date] Tagged ('$date')
  /*User added date components*/
  ,Dual(Year($1), if(Year($1)=Year(today()),YearStart($1),null)) AS [ThisYear] Tagged ('$axis', '$thisyear')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),if(Year($1)=Year(today()),QuarterStart($1),null)) AS [ThisYearQuarter] Tagged ('$axis', '$thisyearquarter')
  ,Dual(Year($1)&'-'&Month($1), if(Year($1)=Year(today()), monthstart($1),null)) AS [ThisYearMonth] Tagged ('$axis', '$thisyearmonth')
  ,Dual(Year($1), if(Year($1)=(Year(today())-1),YearStart($1),null)) AS [LastYear] Tagged ('$axis', '$lastyear')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)) if(Year($1)=(Year(today())-1),QuarterStart($1),null)) AS [LastYearQuarter] Tagged ('$axis', '$lastyearquarter')
  ,Dual(Year($1)&'-'&Month($1), if(Year($1)=(Year(today())-1), monthstart($1),null)) AS [LastYearMonth] Tagged ('$axis', '$lastyearmonth')
  ,Dual(date(MonthStart($1),'MMM-YYYY'), if(Monthstart($1)=Monthstart(today()),Monthstart($1),null)) AS [ThisMonth] Tagged ('$axis', '$thismonth')
  ,Dual(date(MonthStart($1),'MMM-YYYY'), if(Monthstart($1)=Monthstart(addmonths(today(),-1)),Monthstart($1),null)) AS [LastMonth] Tagged ('$axis', '$lastmonth')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),if(QuarterStart($1)=QuarterStart(Today()),QuarterStart($1),null)) AS [ThisQuarter] Tagged ('$axis', '$thisquarter')
  ,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),if(QuarterStart($1)=QuarterStart(addmonths(Today(),-3)),QuarterStart($1),null) ) AS [LastQuarter] Tagged ('$axis', '$lastquarter')
  ,Dual(date(MonthStart($1),'MMM-YYYY'),if(QuarterStart($1)=QuarterStart(Today()),MonthStart($1),null)) AS [ThisQuarterMonth] Tagged ('$axis', '$thisquartermonths');

DERIVE FIELDS FROM FIELDS [DATEFIELDS HERE],[DATEFIELDS HERE]
                        USING [Calendar] ;


To use this code, drop it in your script after your tables are loaded and define the fields you want to apply this to! And that's it, you will then have all the alternative date options in your field list.
Here are a few of the fields that have been generated automatically, I think this not a replacement for the old master calendar but it does expand that function in a clean and user friendly way.