topic Re: First sorted value in QlikView

First sorted value

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

Hi guys,

Any idea why this will not work?

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

This is driving me mad!

Thanks

Re: First sorted value

sunny_talwar — Tue, 17 Apr 2018 11:09:16 GMT

Because FirstSortedValue and Sum are both Aggregation function and when they have to be used together need Aggr() function between them.....

Re: First sorted value

sunny_talwar — Tue, 17 Apr 2018 11:09:36 GMT

What exactly are you trying to do here?

Re: First sorted value

jamelmessaoud — Tue, 17 Apr 2018 11:14:03 GMT

I see. Me being a bit dim then!

I will do a bit more messing around to try and find a solution.

Thanks as always Sunny

Re: First sorted value

itec_pao — Tue, 17 Apr 2018 11:32:36 GMT

Hi all,

The use of firstsortedValue() with aggr would be like:

FirstSortedValue(Dimension, -Aggr(Expression, Dimension, ChartDimension)

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

(The - sign is for descending, so that you get the top/best ones first)

Re: First sorted value

jamelmessaoud — Tue, 17 Apr 2018 11:35:29 GMT

Hi Oktay,

I would like to show the lowest Drop Rate instead of the Sub Contractor.

The drop rate is a calculation which is:

Sum({<[Sub Contractor Type] = {'DropRate'}>}[Sub Contractor Rate])

So i am guessing what I want to achieve is not possible by firstsortedvalue function?

Thanks

Re: First sorted value

itec_pao — Tue, 17 Apr 2018 11:40:59 GMT

Hi Jamel,

What is your chart dimension? If it is Sub Contractor, then min({<[Sub Contractor Type] = {'DropRate'}>}[Sub Contractor Rate]) would be sufficient. However, having different dimensions additionally, then firstsortedValue() is the one you need.

What is exactly what you try to calculate? Dimension and Expressions please

Regards,

OKtay

Re: First sorted value

sunny_talwar — Tue, 17 Apr 2018 11:41:01 GMT

May be just this then

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

Re: First sorted value

jamelmessaoud — Tue, 17 Apr 2018 11:49:25 GMT

Both of your solutions did exactly what i wanted

It is a shame I cannot set both to correct!

Thanks very much for your help guys