1 Reply Latest reply: Jun 28, 2016 11:08 AM by Marcus Sommer RSS

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

    Ruth Hopkins


      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?