topic Re: Obtain field with maximum value in QlikView

Obtain field with maximum value

arpitkharkia — Mon, 02 Jan 2017 08:28:26 GMT

Please consider the following example:

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 — Mon, 02 Jan 2017 08:42:27 GMT

for finding the Category with the highest avg

use this expression :

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

Anil_Babu_Samineni — Mon, 02 Jan 2017 09:21:44 GMT

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 — Mon, 02 Jan 2017 09:24:58 GMT

Hi,

Check this

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

As Exression in chart or Table.

-Hirish

MK_QSL — Mon, 02 Jan 2017 10:09:54 GMT

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

=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 — Mon, 02 Jan 2017 14:59:05 GMT

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 — Wed, 04 Jan 2017 05:38:59 GMT

Thank you everyone for the help!