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:

       

      Forecasttemp:

      Crosstable (ForecastDate, ForecastUtilization, 2)

      Load *

      FROM [lib://source]

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

       

      ForecastData:

      noconcatenate

        Load

        AutoNumber(Port) as PortKey,

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

              ForecastUtilization,

      Resident Forecasttemp;

      drop table Forecasttemp;

       

      ReportDateTemp:

        Load

              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). 

       

      ReportDate.JPG

       

      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.

      Help?