topic Re: Rank () - Not working as expected in QlikView

Rank () - Not working as expected

anagharao — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I have a straight table with Employee, Manager, LOB and Amount (spend)details. I am trying to show only the top 10 of the sum amount using the below formula:

SUM({<Employee={"=Rank(Sum([AMOUNT]))<10"}>}Amount)

However this doesn't work. Also if i move the value to 15 or 20 i see all records or some records in the middle skipped.

Thank you for your help

Regards,

Anagha

Re: Rank () - Not working as expected

Anil_Babu_Samineni — Mon, 11 Jul 2016 08:56:34 GMT

Like this.

Re: Rank () - Not working as expected

tamilarasu — Mon, 11 Jul 2016 08:59:35 GMT

Try this expression.

You have entered the "Amount" field in caps. That causes the issue.

SUM({<Employee={"=Rank(Sum([Amount]))<=10"}>}Amount)

Re: Rank () - Not working as expected

Chanty4u — Mon, 11 Jul 2016 09:03:22 GMT

try this,

Sum({<Employee={'>$(=sum(Amount,10))'}>} amount)

Edited:

Re: Rank () - Not working as expected

tyagishaila — Mon, 11 Jul 2016 09:16:09 GMT

This expression is giving correct result.

I think you are using field name "AMOUNT" while it is "Amount"

Re: Rank () - Not working as expected

fneumann — Mon, 11 Jul 2016 09:47:54 GMT

As an alternative, you can also put the rank()-function as a dynamic dimension like this:

aggr(if(Rank(Sum([Amount]))<=10, Employee,'Others'),Employee)

In the formular, you just put Sum([Amount])

Re: Rank () - Not working as expected

tyagishaila — Mon, 11 Jul 2016 10:10:41 GMT

find attached file for solution

Re: Rank () - Not working as expected

effinty2112 — Mon, 11 Jul 2016 10:57:35 GMT

Hi Anagho

Does this straight table give you what you need?

Employee	Sum({$<Employee = {"=Aggr(Rank(Sum(Amount),0,1), Employee) <= 10"}>}Amount)
Amir	11321
Ambika	8798
Arjun	8798
Ait	7894
Amit	5568
Lekha	5487
Ram	4987
Airt	4984
Rekha	4785
Anjan	1654