If there are duplicate values, how to display the top three

[Mikiyo]

Hi,i am new to community,there is a questin whitch bother me for sevaral days,can anyone help me ? thanks in advance.

now ,i have some data like this:

code    type    qty

DG0219   A      2

DG0219  B      30

DG0219  C    1

DG0219   D   2

DG0219   E   1

DG0219   F   1

DG0219   G   1

DG0219    H   1

what i want to display is like this:

code      NO.1 type     NO.2 type     NO.3 type

DG0219       B                    A;D               C;E;F;G;H

I tried these:

=aggr(contact(if(aggr(rank(Qty,1),code,type)=1,type),';'),code)

=aggr(contact(if(aggr(rank(Qty,1),code,type)=2,type),';'),code)

=aggr(contact(if(aggr(rank(Qty,1),code,type)=3,type),';'),code)

to show NO.1,NO.2,NO.3,but what i can got just like this:

code      NO.1 type     NO.2 type     NO.3 type

DG0219       B                    A;D               C;E;F;G;H

i guess maybe there was tied for third place so it can't  show all of them,so how can i get the proper rank?

[PrashantSangle · Accepted solution]

I think a simple max() will work for you

try below 3 expression

1: No1 Type :  Concat({<qty={"$(=max(qty,1))"}>}type,';')

2: No2 Type : Concat({<qty={"$(=max(qty,2))"}>}type,';')

3: No3 Type :Concat({<qty={"$(=max(qty,3))"}>}type,';')

Regards,

Prashant Sangle

[rubenmarin · Accepted solution]

Hi, this is beacuse 2nd and 3rd are tied,so from rank 2 it jumps to rank 4, something similar might happen if there are more than one in first position.

For this data this might work:

=concat(aggr(if(qty=Max(Total <code> qty),type),code,type),';')

=concat(aggr(if(qty=Max(Total <code> qty,2),type),code,type),';')

=concat(aggr(if(qty=Max(Total <code> qty,3),type),code,type),';')

[Mikiyo]

That works great,thank u,Rubenmarin,This makes me have a deeper understanding of rank function and Max function