Hi, I thought this formula was only supposed to give the most current record for a given user, but it's not,
firstsortedvalue(if(TestDate>=vCohort1MinDate and TestDate<=vCohort1MaxDate, BMI),-(TestDate))
It is on a straight table like this
Client User BMI
ABC 123 -
ABC 456 24.30
Now User 123 has 2 records on two different days within the date range, but both have the identical BMI value as far as I can see. Firstsortedvalue() shouldn't care either way and should just give me whichever one is most current, but it is not in this case.
I have a related input box to make selections based on a specified date range (using vMinDate and vMaxDate) that currently matches the date range of the input box used for this table (vCohort1MinDate and vCohort1MaxDate), so it shouldn't be trying to display anything outside the date selection range.
Now, when I add the TestDate field to the table, I get this:
Client User TestDate BMI
ABC 123 11/9/2012 2:40:35 PM 25.45
ABC 123 11/6/2012 11:28:15 AM 25.45
ABC 456 11/6/2012 11:35:58 AM 24.30
And if I take the testdate field back out User 123's BMI drops back to null -
Can someone help me with my firstsortedvalue function? Firstsortedvalue()'s just supposed to get the first one in the sort order... Thanks!
(FYI, The present workaround for me is to leave the testdate field in. export to excel, and remove duplicates there.)
)