topic Re: Question on how to select a max value from a data set using a date threshold limiter in QlikView

Question on how to select a max value from a data set using a date threshold limiter

Anonymous — Wed, 12 Aug 2015 12:10:05 GMT

All,

I'm having trouble identifying the maximum value from a data set when I try to use Set Analysis to create a date threshold. Here is a sample data set that I am working with:

LOAD * INLINE [

DailyValue, DailyDate

60, 42206

65, 42207

55, 42208

50, 42209

45, 42210

40, 42211

30, 42212

25, 42213

20, 42214

10, 42215

100, 42216

105, 42217

95, 42218

90, 42219

97, 42220

80, 42221

75, 42222

70, 42223

];

What I want to do is identify the maximum value in the DailyValue field, for a subset of the above table, where the DailyDate is no more than 10-days from today's date.

In order to obtain my answer, I have tried various expressions in a text box. In all of my attempts, I either return a " - " in the text box, or the maximum value in the entire table.

Here is the latest expression that I'm not having any luck with:

=MAX(AGGR({$<DailyDate={">=$(=NUM(TODAY())-10)">} DailyValue))

If Today (8/12/2015) is 42228, then Num(Today())-10 would be 42218. Using the logic above, I'm trying to return a value of "97", since that would be the largest DailyValue where the DailyDate value is 42218 or larger.

Thanks in advance for any help on this.

Re: Question on how to select a max value from a data set using a date threshold limiter

Anonymous — Wed, 12 Aug 2015 12:14:43 GMT

this should work, no Need for aggr

=MAX({<DailyDate={">=$(=NUM(TODAY())-10)"}>} DailyValue)

Re: Question on how to select a max value from a data set using a date threshold limiter

Kushal_Chawda — Wed, 12 Aug 2015 12:24:47 GMT

try,

=max({<DailyDate={">=$(=today()-10))"}>}DailyValue)

Re: Question on how to select a max value from a data set using a date threshold limiter

Anonymous — Wed, 12 Aug 2015 12:26:57 GMT

Thanks for the reply. When I tried your expression the value that was returned was "105".

Re: Question on how to select a max value from a data set using a date threshold limiter

Anonymous — Wed, 12 Aug 2015 12:27:48 GMT

Thanks for your reply. That worked perfectly. For some reason I had it in my head that I needed to use AGGR first. Thanks for setting me straight.

Re: Question on how to select a max value from a data set using a date threshold limiter

Anonymous — Wed, 12 Aug 2015 12:33:41 GMT

you are welcome

glad to help you