topic Identifying the group with the max value in QlikView

Identifying the group with the max value

Fri, 31 Jan 2014 12:03:27 GMT

Hi,

i have teh following table:

Group Sales

Sports 50

Sports1 100

sports2 30

i have 2 text objects in which i have 2 show the group with the maximum sales and the sales for that particular group

Maximum sales can be obtained using the below expression:

=Num(Max(Aggr(Sum(Sales), Group)), '###,##0')

what would be the expression for finding its corresponding group (in this case sports1)

thanks

Fri, 31 Jan 2014 12:18:35 GMT

Hi Yousuf

Is this what you need?

=Only({<Sales = {"$(=Max(Sales))"}>} Group)

Lukasz

Fri, 31 Jan 2014 12:20:04 GMT

Hi,

There would be FirstSortedValue but it would return null if more than 1 group had achieved the max sales.

You can obtain a concatenation of all the winning groups like this:

=Concat({<Group={"=Sum(Sales)=Max(TOTAL Aggr(Sum(Sales), Group))"}>} DISTINCT Group,', ')

Winning groups are separated by ', ' only if there is more than 1. This formula would also work without sales being summed by group in the table.

Hope this helps.

Fri, 31 Jan 2014 12:31:27 GMT

Thanks Likasz

Fri, 31 Jan 2014 12:31:55 GMT