topic Re: Getting top 3 ranks in textbox in QlikView

Getting top 3 ranks in textbox

mdb_blin — Tue, 11 Sep 2018 23:38:49 GMT

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

Re: Getting top 3 ranks in textbox

tush — Wed, 12 Sep 2018 01:55:45 GMT

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

Re: Getting top 3 ranks in textbox

tresB — Wed, 12 Sep 2018 04:49:13 GMT

Try like:

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

Re: Getting top 3 ranks in textbox

gawalimegha — Wed, 12 Sep 2018 07:39:46 GMT

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

Re: Getting top 3 ranks in textbox

mdb_blin — Wed, 12 Sep 2018 18:53:33 GMT

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

Re: Getting top 3 ranks in textbox

tresB — Thu, 13 Sep 2018 05:33:36 GMT

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.

Re: Getting top 3 ranks in textbox

mdb_blin — Mon, 17 Sep 2018 18:10:18 GMT

Thank you very much Tresesco. Worked perfectly!