Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try like:
=Concat(DISTINCT {<[Item Name]={"=Rank(Sum(Sales))<4"}>} [Item Name], chr(10) , -Sales)
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))
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 |
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.
Thank you very much Tresesco. Worked perfectly!