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

[Anonymous]

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.

[Anonymous · Accepted solution]

this should work, no Need for aggr

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

[Kushal_Chawda]

try,

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

you are welcome

glad to help you