Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jamelmessaoud
Creator II
Creator II

First sorted value

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

J

1 Solution

Accepted Solutions
itec_pao
Partner - Creator
Partner - Creator

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

View solution in original post

8 Replies
sunny_talwar

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

sunny_talwar

What exactly are you trying to do here?

jamelmessaoud
Creator II
Creator II
Author

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

itec_pao
Partner - Creator
Partner - Creator

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)

jamelmessaoud
Creator II
Creator II
Author

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

itec_pao
Partner - Creator
Partner - Creator

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

sunny_talwar

May be just this then

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

jamelmessaoud
Creator II
Creator II
Author

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

J