Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

arpitkharkia
New Contributor II

Obtain field with maximum value

Please consider the following example:

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?

1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Re: Obtain field with maximum value

hi

for finding the Category with the highest avg

use this expression :

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

6 Replies
lironbaram
Honored Contributor II

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

Capture.PNG

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

Life is so rich, and we need to respect to the life !!!
hirishv7
Honored Contributor

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)

surendraj
Valued Contributor

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

arpitkharkia
New Contributor II

Re: Obtain field with maximum value

Thank you everyone for the help!

Community Browser