Obtain field with maximum value

[arpitkharkia]

Please consider the following example:

Category	Value
A	10%
B	20%
B	22%
A	20%

I will be loading the above table directly into Qlik. What I want is to first have an average of Value so that the following table is obtained:

Category	Value
A	15%
B	21%

from this now I have to find the name of the category having the highest value. So, from table1 I have to obtain Category B as the answer.

How to perform the above using Set Analysis or/and Back-end scripting?

[lironbaram · Accepted solution]

hi

for finding the Category with the highest avg

use this expression :

=FirstSortedValue(Category,-aggr(avg(Value),Category))

[Anil_Babu_Samineni]

Why can't you create Simple

Num(Avg(Value),'##,##0%')

For second one try like below

For Max =FirstSortedValue(Category,Aggr(Num(Avg(Value),'##,##0%'),Category))

For Min =FirstSortedValue(Category,-Aggr(Num(Avg(Value),'##,##0%'),Category))

[HirisH_V7]

Hi,

Check this

=Num(rangeavg(Aggr(Above(sum(Value),0,rowno()),Category))/Count(Category),'#,##0%')

As Exression in chart  or Table.

-Hirish

[MK_QSL]

=FirstSortedValue(Distinct Category,-Aggr(Avg(Value),Category))

or

=FirstSortedValue(Category,-Aggr(Avg(Value),Category))

and if you have more than one category having same average.. you can use below to get more than one category..

=Concat({<Category = {"=Rank(Avg(Value))=1"}>}DISTINCT Category)

[surendraj]

Hi Arpit,

I tried this,Its working fine.

=FirstSortedValue({<Category>}Category,-Aggr(max({<Category>}Value),Category))

By using this you can achieve Category B as the answer even if you made the selection on Category it won't change.

--Surendra

[arpitkharkia]

Thank you everyone for the help!