topic Re: Show Group that has max quantity in QlikView

Show Group that has max quantity

Wed, 01 Jul 2015 06:47:44 GMT

Hi,

I have a question, i have a data like this:

Menu Quantity GroupName

A 5 Group1

B 10 Group1

C 2 Group1

B 1 Group2

A 7 Group2

i want to show group name that has max quantity, Total quantity of group1 is 10+5+2 = 17 and Total of group2 is 1+7 = 8, So i want to show 'Group1' that has maximum total/sum quantity in a text Object. Can you show me the method ?

Thanks in advance.

Re: Show Group that has max quantity

Chanty4u — Wed, 01 Jul 2015 06:49:10 GMT

can u attach the sample qvw..?

Thank you

Suresh

Re: Show Group that has max quantity

MayilVahanan — Wed, 01 Jul 2015 06:55:09 GMT

Try like this

=FirstSortedValue(GroupName, - Quantity)

Re: Show Group that has max quantity

tresB — Wed, 01 Jul 2015 06:57:44 GMT

In the textbox:

=FirstSortedValue(GroupName, -Aggr(Sum(Quantity),GroupName))

Re: Show Group that has max quantity

sunilkumarqv — Wed, 01 Jul 2015 06:59:38 GMT

try like this

=sum({<GroupName={'Group1'}>}Quantity)/sum(TOTAL Quantity)

Re: Show Group that has max quantity

Wed, 01 Jul 2015 07:09:30 GMT

Hi guys,

Thanks for all your help. But i have another problem, when there are two group that has same total quantity, let's say both Group1 and Group2 has total quantity 20. The result is -

Do you know why ?

Thanks

Re: Show Group that has max quantity

MayilVahanan — Wed, 01 Jul 2015 07:11:08 GMT

If both values are same, then Firstsortedvalue shows null value.

In that case, which value you need to show in textbox?

Re: Show Group that has max quantity

Wed, 01 Jul 2015 07:13:13 GMT

Hi Mayil,

Is it possible to show both value, so the result will be 'Group1' and 'Group2'

thanks for your response

Re: Show Group that has max quantity

SatyaPaleti — Wed, 01 Jul 2015 07:15:22 GMT

Hi Indra,

Try with this

Max(aggr(sum(Quantity), GroupName))

Thank you,

Satya Paleti

Re: Show Group that has max quantity

tresB — Wed, 01 Jul 2015 07:20:26 GMT

Just put Distinct like:

=FirstSortedValue(Distinct GroupName, -Aggr(Sum(Quantity),GroupName))

and don't worry about the synatx warning in red.

Re: Show Group that has max quantity

MayilVahanan — Wed, 01 Jul 2015 07:21:43 GMT

Try like this

=Concat(DISTINCT{<GroupName = {"=Sum(Quantity)=$(vMaxQuantity)"}>}GroupName,',')

where vMaxQuantity =Max(aggr(sum(Quantity), GroupName))

Hope it helps

Re: Show Group that has max quantity

Wed, 01 Jul 2015 07:21:47 GMT

Hi Satya,

thanks for your comments, but i want to show the group name for ex : 'Group1', not the max quantity.

Thanks

Re: Show Group that has max quantity

jagan — Wed, 01 Jul 2015 07:26:53 GMT

Hi Indra,

Try this

=Concat(Aggr(If(Rank(Sum(Quantity)) = 1, GroupName), GroupName), ',')

If there are groups with same quantity it will give as comma separated.

Regards,

Jagan.

Re: Show Group that has max quantity

Wed, 01 Jul 2015 07:26:59 GMT

Hi Mayil,

You're a great problem solver It works and solve my problem.

Thanks in advance

Re: Show Group that has max quantity

jagan — Wed, 01 Jul 2015 07:31:21 GMT

Hi,

Tried this, even simpler

=Concat(Aggr(If(Rank(Sum(Quantity)) = 1, GroupName), GroupName), ',')

Regards,

Jagan.

Re: Show Group that has max quantity

Wed, 01 Jul 2015 07:41:47 GMT

Wow, you guys are rock.

Thanks Jagan for your suggestion.

Really appreciate your help

Re: Show Group that has max quantity

SatyaPaleti — Wed, 01 Jul 2015 07:49:23 GMT

Hi Indra,

Try this if you want to show only Group1 Quantity

=sum({<GroupName = {'Group1'}>}Quantity)

Output

Thank you,

Satya Paleti

Re: Show Group that has max quantity

SatyaPaleti — Wed, 01 Jul 2015 07:55:22 GMT

Indra,

Try with this

=FirstSortedValue(GroupName,-Aggr(Sum(Quantity),Menu))

Thank you,

Satya Paleti