Min Max Date Set Analysis Failure

joey_lutes — Thu, 01 Sep 2016 22:30:26 GMT

Something that I would have thought should take me 30 seconds to write has now caused me multiple hours of research and dozens of iterations.

Simple I need 2 numbers:

Sum({$<ForecastDate = {'$(=Max({1}ForecastDate))'}>}ForecastUtilization)

Sum({$<ForecastDate = {'$(=Min({1}ForecastDate))'}>}ForecastUtilization)

With or without the {1}, adding quotes, removing them, changing around DSE, etc. . . .

SO, I went the variable route:

ReportDateTemp:

Load

max(ReportDate) as MaxReportDate,

min(ReportDate) as MinReportDate,

max(ForecastDate) as MaxForecastDate,

min(ForecastDate) as MinForecastDate

Resident ForecastData;

Let vMaxReportDate = Peek('MaxReportDate');

Let vMinReportDate = Peek('MinReportDate');

Let vMinForecastDate = Peek('MinForecastDate');

Let vMaxForecastDate = Peek('MaxForecastDate');

Drop Table ReportDateTemp;

Now I have vMinForecastDate and vMaxForecastDate = both of which display perfectly in a KPI box.

The set analysis is still defeating me.

I've tried:

Sum({$<ForecastDate = {'$(=$(vMinForecastDate))'}>}ForecastUtilization) //returns 0

Sum({$<ForecastDate = {'$(vMinForecastDate)'}>}ForecastUtilization) //returns 0

I'm sure it's silly. Hoping so, Help?

Re: Min Max Date Set Analysis Failure

sunny_talwar — Thu, 01 Sep 2016 23:43:35 GMT

May be try using Date() function around your variable with the date format of your date field. I think the format mis-match might be causing this

joey_lutes — Fri, 02 Sep 2016 00:09:54 GMT

Yup, that was it. Glad it was simple. Live and learn.

Thanks again You rock!