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

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?

[sunny_talwar]

How about this:

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

and this for minimum

Avg({$<ReportDate = {"$(=Date(Min(ReportDate)))"}>}Slope)

[sunny_talwar · Accepted solution]

Or this with variable

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

[joey_lutes]

You're amazing.

I'll add this to the archives and hopefully learning will occur