Rank () - Not working as expected

[anagharao]

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

[Anil_Babu_Samineni]

Like this.

[tamilarasu · Accepted solution]

Try this expression.

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

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

[Chanty4u]

try this,

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

Edited:

[tyagishaila]

This expression is giving correct result.

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

[fneumann]

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

[tyagishaila]

find attached file for solution

[effinty2112]

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