How to get date for a max value in straighttable?

[baylor2016]

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.

ID	pat_id	Date	BMI
1	1	1/1/2016	30
2	1	1/2/2017	39
3	1	1/3/2018	20
4	2	1/4/2016	28
5	2	1/5/2017	24
6	2	1/6/2018	33
7	3	1/7/2011	28
8	3	1/8/2012	40
9	3	1/9/2015	36
10	3	1/10/2017	47
11	3	1/11/2013	28
12	4	1/12/2014	27
13	4	1/13/2015	44
14	4	1/14/2016	37
15	4	1/15/2017	35
16	4	1/16/2018	31

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

[vishsaggi · Accepted solution]

Try this for your Max BMI date

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

[baylor2016]

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