Get value for record with most recent date

[Lauri]

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

[jonathandienst · Accepted solution]

With PatientID as a dimension, you can use

=FirstSortedValue(ObservationValue, -ObservationDate)

to find the most recent observation value

[Lauri]

Yes! Thanks so much, Jonathan. I figured there had to be a simpler solution!