Getting top 3 ranks in textbox

[mdb_blin]

I'm trying to get the top three item names in a text box, ranked by its Sales.

Item Name	Sales
Item A	500
Item B	300
Item C	700
Item D	900
Item E	200

Therefore, I would want my Text box to appear as the below without the sales value.

Top 3 Selling Items

Item D

Item C

Item A

[tush]

Hi,

     Please Check this expression

=Concat(If(Aggr(Rank(Sales),[Item Name]) <=3, [Item Name]))

Note : Replace Sales and Item Name with you actual fields, it will give you the Top 3 Item Name name.

Regards:

Tushar

[tresesco]

Try like:

=Concat(DISTINCT {<[Item Name]={"=Rank(Sum(Sales))<4"}>} [Item Name], chr(10) , -Sales)

[gawalimegha]

Hello,

Try this one.

=only(if(aggr(Rank(Sum(Sales)),Product)=1,Product)) &  chr(10) &only(if(aggr(Rank(Sum(Sales)),Product)=2,Product)) 

& chr(10)& only(if(aggr(Rank(Sum(Sales)),Product)=3,Product))

[mdb_blin]

Hi Tresesco,

Does date in the sales data affect the formula?

Can you help provide updated formula for the data set below?

Top 3 should now be Item D, Item C, Item E

Date	Item Name	Sales
1/31/2018	Item A	500
1/31/2018	Item B	300
1/31/2018	Item C	700
1/31/2018	Item D	900
1/31/2018	Item E	200
2/28/2018	Item A	200
2/28/2018	Item B	300
2/28/2018	Item C	400
2/28/2018	Item D	500
2/28/2018	Item E	800

[tresesco]

You can get the items easily (without sorting them) using:

=Concat(DISTINCT {<[Item Name]={"=Rank(Sum(Sales))<4"}>} [Item Name], chr(10))

however, if you want them to be sorted according to their rank, you have to use another aggregation function (since Items have multiple sales values in this case), like:

=Concat(DISTINCT {<[Item Name]={"=Rank(Sum(Sales))<4"}>} [Item Name],

chr(10), -Aggr(NODISTINCT Sum(Sales),  [Item Name]) )

Else, to avoid this additional aggr() expression, you could use three expressions for three Items.

[mdb_blin]

Thank you very much Tresesco.  Worked perfectly!