2 Replies Latest reply: May 3, 2018 9:02 AM by Lauri Scharf RSS

    Get value for record with most recent date

    Lauri Scharf

      I have an app with one table with blood pressure readings for many patients. FIelds are PatientID, ObservationValue, ObservationDate, and RowID (created using RowNo()).

       

      I want to show a table with every PatientID and his/her most recent ObservationValue (based on ObservationDate).

       

      PatientID is a dimension in the table.

       

      I've tried various ways to get the ObservationValue, to no avail:

      =max({$<ObservationDate={"$(=max(aggr(max(ObservationDate), PatientID))"} >} ObservationValue)

      =max({$<ObservationDate={"$(=aggr(max(ObservationDate), PatientID)"} >} ObservationValue)

       

      and as a dimension:

      =Aggr(max({$<ObservationDate={"$(=aggr(max(ObservationDate), PatientID)"} >} ObservationValue), PatientID)

       

      The above all return null.

       

      This returns each patient's max(ObservationValue) and ignores the ObservationDate, so it's wrong:

      =max({$<ObservationDate={"=$(=max(ObservationDate))"} >} num(ObservationValue))

       

      I must be over-complicating this...