Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

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

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

Community Browser