Top performers in a text box

[#1]

Hi

I have been facing in a problem wherein I have to show the sum of sales of top 10 customers in a text box. I tried it using rank in set analysis using the expression:

= Sum({<Customer= {“=rank(sum(DOLLAR),4)<=10”}>} DOLLAR) .

But I found out that this function works only in the Expression part of a chart and not in a text object

I also found out a way in which I could get the sales of customer having 10th rank:

=max((Aggr(sum(DOLLAR), Customer)),10)

I could use this for 1,2,..10 ranks and get a sum of it but I dont want to use I need to show a similar data for top 100 and 1000 customers as well and this is not a scalable approach.

Needed a help on how to achive the above functionality.

Thanks in Advance

Himanshu Gupta

[lawrenceiow · Accepted solution]

I used your formula in this way and it worked for me:

=' Top 10 = ' & (Sum({<Customer= {“=rank(sum(DOLLAR),4)<=10”}>} DOLLAR))

All you should need is =(with your formula here)

[#3]

Hi Himanshu,

Use below expression in the Textbox:

=SUM(Aggr(IF(Aggr(Rank(SUM(Dollar)),Customer)<=10,SUM(Dollar)),Customer))

Instead of 10, you can create an input variable with vTOP and use $(vTOP).

=SUM(Aggr(IF(Aggr(Rank(SUM(Dollar)),Customer)<=$(vTOP),SUM(Dollar)),Customer))

Thanks

Sabal