Show Group that has max quantity

[#1]

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.

[MayilVahanan · Accepted solution]

Hi

Try like this

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

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

Hope it helps

[#3]

Hi Satya,

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

Thanks

[jagan]

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.

[#5]

Hi Mayil,

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

Thanks in advance

[jagan]

Hi,

Tried this, even simpler

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

Regards,

Jagan.

[#7]

Wow, you guys are rock.

Thanks Jagan for your suggestion.

Really appreciate your help

[SatyaPaleti]

Hi Indra,

Try this if you want to show only Group1 Quantity

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

Output

Thank you,

Satya Paleti

[SatyaPaleti]

Indra,

Try with this

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

Thank you,

Satya Paleti