Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try
=firstsortedvalue({<CP_EFFECTIVE_DATE={"<=$(=date(vBenchmarkDate))"}>} CP_AMOUNT, -CP_EFFECTIVE_DATE)
Note also the minus befor CP_EFFECTIVE_DATE to get the max.
try
=firstsortedvalue({<CP_EFFECTIVE_DATE={"<=$(=date(vBenchmarkDate))"}>} CP_AMOUNT, -CP_EFFECTIVE_DATE)
Note also the minus befor CP_EFFECTIVE_DATE to get the max.
That works!!! Thank you so much!!!
Will you explain exactly how this is interpreted and why it works? Just curious...
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