How to get date for a max value in straighttable?

baylor2016 — Wed, 24 Oct 2018 21:34:22 GMT

Hi,

I need to get the date for Max value by different users. In my example, I would like to know when is the patient's highest BMI.

Here is the data table.

The max BMI date for pat_id 1 is 1/2/2017, and for pat_id 2 is 1/8/2018.

I tried to use max({<BMI={"=max(BMI)"}>}Date), it just returns the max date - not the date for max BMI.

Please advise.

The same project attached.

Thanks

Re: How to get date for a max value in straighttable?

vishsaggi — Wed, 24 Oct 2018 22:01:38 GMT

Try this for your Max BMI date

= Firstsortedvalue(Date(Date,'M/D/YYYY'), -Aggr(Max(BMI), pat_id, BMI))

baylor2016 — Thu, 25 Oct 2018 01:20:00 GMT

Thank you! It works perfectly. Could you give some explanations?