How to make rank() works in a text field

[rcandeo]

I have a table that shows informations for a client like this:

Client     John Denver

Rank               5

Sales          $5,500

Growth          35%

But I need to put this infornation separetelly. So each one will be in a text field.

The problem is that the second line where I have the Rank information, it does not worl as Rank is made to work only in tables.

How can I show this rank information please?

The expression is this:

=rank(sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[Client]={'$(vSelectedClient)'}>} $(vCurrency))) * avg(1)

[sunny_talwar]

You will need to use Aggr() function here:

Aggr(Rank(Sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[Client]={'$(vSelectedClient)'}>} $(vCurrency))), DimensionsFromChart)

DimensionFromChart is a placeholder and you need to replace it with the dimensions from your char where you have previously calculated your Rank for each client.

[rcandeo]

My original chart has only one dimension that is Client. I put this dimension but I continue seeing 1 as rank answer don´t matter wich Client I choose:

This is the new expression:

=Aggr(rank(sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[Broker Group Name]={'$(vSelectedBroker)'}>} $(vCurrency))), [Broker Group Name])

[sunny_talwar]

Would you be able to share a sample where this isn't working

[antoniotiman]

Hi Robson,

Try

=rank(Aggr(sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'},[Broker Group Name]={'$(vSelectedBroker)'}>} $(vCurrency)),Client))

[rcandeo]

sorry, it continues bringing me 1 as result

[rcandeo]

I´ll build one and send in some minutes. thank you

[sunny_talwar]

Sounds good

[rcandeo]

here it is.

On the left there is the new information. See that Rank is always 1.

On the right the information in a table. Rank works there.

You only need to choose a broker

[sunny_talwar · Accepted solution]

This worked:

=Aggr(Rank(Sum({1<[Reporting Calendar Year]={'$(vSelectedYear)'},[Reporting Calendar Quarter]={'$(vSelectedQuarter)'}>} $(vCurrency))) * Avg(1), [Broker Group Name])