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

Tue, 28 Jun 2016 01:06:05 GMT

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?

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

marcus_sommer — Tue, 28 Jun 2016 15:08:37 GMT

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

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

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

- Marcus

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

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

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