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...