Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mdb_blin
New Contributor

Getting top 3 ranks in textbox

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

Item NameSales
Item A500
Item B300
Item C700
Item D900
Item E200

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

6 Replies
tush
Contributor II

Re: Getting top 3 ranks in textbox

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

MVP
MVP

Re: Getting top 3 ranks in textbox

Try like:

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

Capture.JPG

gawalimegha
New Contributor III

Re: Getting top 3 ranks in textbox

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
New Contributor

Re: Getting top 3 ranks in textbox

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

   

DateItem NameSales
1/31/2018Item A500
1/31/2018Item B300
1/31/2018Item C700
1/31/2018Item D900
1/31/2018Item E200
2/28/2018Item A200
2/28/2018Item B300
2/28/2018Item C400
2/28/2018Item D500
2/28/2018Item E800
Highlighted
MVP
MVP

Re: Getting top 3 ranks in textbox

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]) )

Capture.JPG

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

mdb_blin
New Contributor

Re: Getting top 3 ranks in textbox

Thank you very much Tresesco.  Worked perfectly!