Date variable in Set Analysis

joey_lutes — Tue, 13 Sep 2016 23:23:18 GMT

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?

Re: Date variable in Set Analysis

sunny_talwar — Tue, 13 Sep 2016 23:32:44 GMT

How about this:

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

and this for minimum

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

Re: Date variable in Set Analysis

sunny_talwar — Tue, 13 Sep 2016 23:33:35 GMT

Or this with variable

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

Re: Date variable in Set Analysis

joey_lutes — Wed, 14 Sep 2016 15:34:33 GMT

You're amazing.

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

topic Re: Date variable in Set Analysis in App Development

Date variable in Set Analysis

Re: Date variable in Set Analysis

Re: Date variable in Set Analysis

Re: Date variable in Set Analysis