How do you retrieve field2 associated with max(field1)
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).
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.
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.