Get value for record with most recent date

Lauri — Thu, 03 May 2018 03:21:04 GMT

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

Re: Get value for record with most recent date

jonathandienst — Thu, 03 May 2018 05:07:48 GMT

With PatientID as a dimension, you can use

=FirstSortedValue(ObservationValue, -ObservationDate)

to find the most recent observation value

Re: Get value for record with most recent date

Lauri — Thu, 03 May 2018 13:02:56 GMT

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

topic Re: Get value for record with most recent date in App Development

Get value for record with most recent date

Re: Get value for record with most recent date

Re: Get value for record with most recent date