topic First Sorted Value (Again!) in QlikView

First Sorted Value (Again!)

jamelmessaoud — Wed, 25 Nov 2020 16:16:04 GMT

Another question.

If this expression brings back more than one result

=firstsortedvalue([Sub Contractor], -Aggr(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]),[Sub Contractor]))

The result is showing null.

Is there a way to show more than one Sub Contractor using this expression?

Thanks

Re: First Sorted Value (Again!)

sunny_talwar — Tue, 17 Apr 2018 13:24:25 GMT

Use Concat() instead

Concat({<[Sub Contractor Type] = {'Base Rate'}>} Aggr(If(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]) = Max(TOTAL Aggr(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), [Sub Contractor])), [Sub Contractor]), [Sub Contractor]), ', ')

Re: First Sorted Value (Again!)

jamelmessaoud — Tue, 17 Apr 2018 13:32:19 GMT

Hi Sunny,

That doesn't quite do what I want. The expression which you helped me with earlier worked perfectly

Min(Aggr(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), [Sub Contractor]))

In an ideal world I want to pull back the Sub Contractors from this same expression if it's possible?

Thanks

Re: First Sorted Value (Again!)

sunny_talwar — Tue, 17 Apr 2018 13:33:54 GMT

You want the sub contractor from the min date or max date? For min... you can try this

Concat({<[Sub Contractor Type] = {'Base Rate'}>} Aggr(If(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]) = Min(TOTAL Aggr(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), [Sub Contractor])), [Sub Contractor]), [Sub Contractor]), ', ')

Re: First Sorted Value (Again!)

jamelmessaoud — Tue, 17 Apr 2018 13:39:50 GMT

It works Sunny. Changed the max to min!!

Concat({<[Sub Contractor Type] = {'Base Rate'}>} Aggr(If(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]) =

min(TOTAL Aggr(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), [Sub Contractor])), [Sub Contractor]), [Sub Contractor]), ', ')

Re: First Sorted Value (Again!)

jamelmessaoud — Tue, 17 Apr 2018 13:40:03 GMT

You beat me to it!