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

stevelord — Mon, 12 Aug 2013 18:53:03 GMT

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

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

stevelord — Mon, 12 Aug 2013 19:19:17 GMT

Please disregard, I pasted a section of the formula into itself somehow or something weird and the formula above is not the one that was in my table expression. Twilight Zone Monday... (I'd delete this if I could, but don't see the option. )

topic Re: Firstsortedvalue() giving nulls when more than one value for a user on different days... in QlikView

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

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