6 Replies Latest reply: Jan 4, 2017 12:38 AM by Arpit Kharkia

# Obtain field with maximum value

CategoryValue
A10%
B20%
B22%
A20%

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:

CategoryValue
A15%
B21%

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?

• ###### Re: Obtain field with maximum value

hi

for finding the Category with the highest avg

use this expression :

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

• ###### Re: Obtain field with maximum value

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))

• ###### Re: Obtain field with maximum value

Hi,

Check this

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

```

As Exression in chart  or Table.

-Hirish

• ###### Re: Obtain field with maximum value

=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)

• ###### Re: Obtain field with maximum value

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

• ###### Re: Obtain field with maximum value

Thank you everyone for the help!