3 Replies Latest reply: Sep 14, 2016 11:34 AM by Joey Lutes RSS

    Date variable in Set Analysis

    Joey Lutes

      I'm really struggling with some date variables and using them in set analysis.

      Here is what I'm working with:



      Crosstable (ForecastDate, ForecastUtilization, 2)

      Load *

      FROM [lib://source]

      (ooxml, embedded labels, table is [Forecast]);





        AutoNumber(Port) as PortKey,

              Date(Date#([Report Date], 'M/D/YYYY')) as ReportDate,


      Resident Forecasttemp;

      drop table Forecasttemp;




              max(ReportDate) as MaxReportDate,

              min(ReportDate) as MinReportDate,

      Resident ForecastData;

      Let vMaxReportDate = Peek('MaxReportDate');

      Let vMinReportDate = Peek('MinReportDate');

      Drop Table ReportDateTemp;


      So I have a ReportDate and vMax and vMinReportDate - obviously they're producing different formats.  I have no idea why.

      In a nutshell, I want to limit expressions by Min and Max ReportDate in set analysis.  Easy, right????

      5+ hours of reading/playing with various combinations and options says it's not.  I hope I'm just an idiot


      Below are KPIs from the 2 methods using the default Qlik Sense 3.0 AUTO number formatting (note different format for same date). 




      Here are a few of the expressions I've tried (I've read HICs articles and dozens of other articles answered by Stefan and Sunnie as well on the topic and I think I've tried all of the options suggested for the individuals to no avail)


      Avg({$<ReportDate = {'$(=$(vMaxReportDate))'}>}Slope)

      Avg({$<ReportDate = {$(=Max(ReportDate))}>}Slope)

      Avg({$<ReportDate = {"=$(=$(vMaxReportDate))"}>}Slope) - this one produces a result, however it not correct - it's averaging data from all report dates, not just the max - which should be just the one number.


      So many $, ', ", (, =$, $= combinations, there doesn't seem to be any hard and fast rules for any of it.