Ranking aggregate values in text object

[#1]

Hi,

I have an aggregate function that gives the following results:

Customer	Product	Sale
X	A	$10
X	B	$20
Y	C	$500
Y	D	$60

My aggregate function is as follows:

aggr(sum(Sale),Customer,Product)

However, I want to put the customer-product combination into a text object, along with where the combination ranks in terms of sale. Example:

If my filter is customer Y, Product C, I want to see the Rank as follows:

Customer-Product     Rank

Y-C                              1

If my filter is Customer Y, Product D, I want to see the Rank as follows:

Customer-Product     Rank

Y-D                              2

Meaning I just want to display the rank in a text object. How can I achieve this? Thanks.

[Kushal_Chawda]

Please see the attached

[sunny_talwar · Accepted solution]

Try this expression:

=If(GetSelectedCount(Product) = 1 and GetSelectedCount(Customer) = 1,

Aggr(If(Sum(Sale)> 0,Rank(TOTAL sum({<Product =, Customer =>}Sale))), Product, Customer))

[#4]

Hi,

I realised you did the ranking via the Script. Is it possible to do it entire in the set analysis instead, rather than a script? Thanks.

[#5]

This works well. Apparently I needed to add in the total keyword in my rank function.