1 Reply Latest reply: Aug 12, 2013 3:19 PM by Steve Lord RSS

    Firstsortedvalue() giving nulls when more than one value for a user on different days...

    Steve Lord

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