Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Or this with variable
Avg({$<ReportDate = {"$(=Date(vMaxReportDate))"}>}Slope)
How about this:
Avg({$<ReportDate = {"$(=Date(Max(ReportDate)))"}>}Slope)
and this for minimum
Avg({$<ReportDate = {"$(=Date(Min(ReportDate)))"}>}Slope)
Or this with variable
Avg({$<ReportDate = {"$(=Date(vMaxReportDate))"}>}Slope)
You're amazing.
I'll add this to the archives and hopefully learning will occur