topic Re: FirstSortedValue Issue in QlikView

FirstSortedValue Issue

Tue, 21 Oct 2014 18:15:44 GMT

Hi Community,

I want to be able to return ID's based on their ranking in the sales field but the issue is when the 2 ID's have the same sales FirstSortedValue returns null instead of the ID. For example FirstSortedValue(Id,Sales,3) and FirstSortedValue(Id,Sales,4) return null. Please advise, thanks!

Keep Qliking!

Re: FirstSortedValue Issue

JonnyPoole — Tue, 21 Oct 2014 18:25:15 GMT

Try adding 'distinct' . The expression editor flags it but its saved my bacon a few times

=FirstSortedValue( distinct ID,-Sales,3)

Re: FirstSortedValue Issue

Tue, 21 Oct 2014 18:29:44 GMT

Thank you, that works to give me one of the keys for the third ranked value, but I still can't get the second key that also has that same third value. Any suggestions?

Re: FirstSortedValue Issue

maxgro — Tue, 21 Oct 2014 18:32:39 GMT

maybe

=FirstSortedValue( Aggr(Concat(ID,','),Sales),-Aggr(Sales,Sales),3)

from here

Firstsortedvalue