Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
triciagdaly
Contributor III
Contributor III

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).

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

try

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

Note also the minus befor CP_EFFECTIVE_DATE to get the max.

View solution in original post

4 Replies
swuehl
MVP
MVP

try

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

Note also the minus befor CP_EFFECTIVE_DATE to get the max.

View solution in original post

triciagdaly
Contributor III
Contributor III
Author

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

triciagdaly
Contributor III
Contributor III
Author

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

swuehl
MVP
MVP

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