Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using First Sorted Value to get a Maximum Value in a Range

I closed the other post.   I have now isolated my issue.  I need to return the Max HistoryID value for each claim.  I have successfully done that using the following expression:

=Max(aggr(firstsortedvalue (HistoryID, -EventDate), ClaimID))

However, what I really need is the Max HistoryID where the corresponding EventDate is <= my end Date range.  I thought I could use the (p) function and replace "HistoryID" with the expression {<HistoryID=P({<EventDate={"<=$(=vEndDate)"}>})>}, to identify those HistoryID's.  My syntax is as follows:

=Max(aggr(firstsortedvalue ({<HistoryID=P({<EventDate={"<=$(=vEndDate)"}>})>}, -EventDate), ClaimID)) 

The syntax appears correct but it doesn't bring anything back.  Any suggestions?

1 Reply
marcus_sommer

I think the p() function isn't right and would rather try this:

=Max(aggr(firstsortedvalue ({< EventDate={"<=$(=vEndDate)"}>} HistoryID, -EventDate), ClaimID))

or

=Max({< EventDate={"<=$(=vEndDate)"}>} aggr(firstsortedvalue (HistoryID, -EventDate), ClaimID))

- Marcus