Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.

Date variable in Set Analysis

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.


1 Solution

Accepted Solutions

Re: Date variable in Set Analysis

Or this with variable

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

3 Replies

Re: Date variable in Set Analysis

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

Or this with variable

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


Re: Date variable in Set Analysis

You're amazing.

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