How do you retrieve field2 associated with max(field1)

[triciagdaly]

I have an input box field with a date value, and deriving a maximum CP_EFFECTIVE_DATE where the CP_EFFECTIVE_DATE <= date from the input box.  The problem is I'm unable to retrieve field (CP_AMOUNT) that is associated with the max(CP_EFFECTIVE_DATE).

Example:

Input box is vBenchmarkDate

In the Expression for CP_EFFECTIVE_DATE, I have:

expression 1:  max({<CP_EFFECTIVE_DATE={"<=$(=date(vBenchmarkDate))"}>}CP_EFFECTIVE_DATE)

The date in vBenchmarkDate is 10/1/2012, and it is correctly pulling the date 8/24/2012, which is the maximum CP_EFFECTIVE_DATE that is <= vBenchmarkDate of 10/1/2012.  in the table, this date has a CP_AMOUNT of .036 associated with it but am having problems retrieving this value.

I have tried setting an expression on CP_AMOUNT with:

expression 2:    firstsortedvalue(CP_AMOUNT,CP_EFFECTIVE_DATE), but it's returning the max(CP_AMOUNT) in the table, and not the one associated with the CP_EFFECTIVE_DATE from expression 1.

Any help would greatly be appreciated.

Tricia

[swuehl · Accepted solution]

try

=firstsortedvalue({<CP_EFFECTIVE_DATE={"<=$(=date(vBenchmarkDate))"}>} CP_AMOUNT, -CP_EFFECTIVE_DATE)

Note also the minus befor CP_EFFECTIVE_DATE to get the max.

[triciagdaly]

That works!!!  Thank you so much!!!

[triciagdaly]

Will you explain exactly how this is interpreted and why it works?  Just curious...

[swuehl]

Consider having a table in your data model with two fields, CP_AMOUNT and CP_EFFECTIVE_DATE.

The expression above will filter your records using the set expression (filtering CP_EFFECTIVE_DATE before vBenchmarkDate), then the function will sort the remaining records based on the second argument ascending and return the value of first argument's expression (field CP_AMOUNT) for the first sorted record.

If you don't use a minus before CP_EFFECTIVE, you will get CP_AMOUNT for the smallest CP_EFFECTIVE, but for the largest when using a minus.

Just create a small table on paper and simulate to see what I mean.

Hope this helps,

Stefan