Ranking position on a text box

[Anonymous]

Hi all,

I need to build a text object showing the position (ranking) of the customer against it's competitors.

Given the following source data:

Customer Valuation

A                90%

B                95%

C                70%

We need that when the user filters a customer, the text box shows it's position (ranking) against the other competitors.

In the example before A is the 2nd, B is the 1st and C is the 3rd, according to the column Valuation. So when the user filters customer A we need to see in the text box something like '#2'.

Thanks in advance!

[sunny_talwar]

Try this kind of expression:

=Aggr(Rank(Sum({<Dim>} Value)), Dim)

Sample Script:

Table:

LOAD * Inline [

Dim, Value

A, 90

B, 95

C, 70

];

[#3]

I would recommend adding an expression for rank using the rank function within set analysis - if you do this I believe you will need to use the '1' option in set analysis to make sure your ranking is based on your entire dataset isntead of what is selected

[Anonymous]

Thanks to both of you, It works perfect!!!

[Anonymous]

Hi Every one again,

as the solution provided works for the initial question, there is another point I did not realize when posting it. The probles is the same, but I need that the rank acts only over some registers. With this sample script:

Sample Script:

Table:

LOAD * Inline [

Dim1, Dim2, Value

A, X, 90

B, X, 95

C, Y, 70

D, X, 60

];

I need that the mentioned expression: =Aggr(Rank(Sum({<Dim1>} Value)), Dim1)  takes into consideration for the ranking just the registers where Dim2 is 'X'.

so in the example provided, for D the ranked value should be 3rd instead of 4th.

Any help?

[sunny_talwar · Accepted solution]

Try this:

=Aggr(Rank(Sum({<Dim1, Dim2 = {'X'}>} Value)), Dim1)

[Anonymous]

Perfect thanks

[Anonymous]

hi,

this post is really useful ,

this syntax also works fine when you select values from a list box,

but when am trying to replicate this logic with hardcoding values for a dimension ,it doesn't seems to be working

fine,can u please help in this regard:

like for eg:rank(count({<dimension>}distinct id),dimension)

this is working fine,so if I select any value from the list box giving the right rank,

but if I do like this:rank(count({<dimension={'value1'}>}distinct id),dimension)

this is not taking the right ranks for the dimension values

(selections made on the sheet for other filters)