topic Re: Ranking position on a text box in QlikView

Ranking position on a text box

Anonymous — Wed, 16 Dec 2015 17:16:35 GMT

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!

Re: Ranking position on a text box

sunny_talwar — Wed, 16 Dec 2015 17:23:10 GMT

Try this kind of expression:

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

Sample Script:

Table:

LOAD * Inline [

Dim, Value

A, 90

B, 95

C, 70

];

Re: Ranking position on a text box

Wed, 16 Dec 2015 17:24:00 GMT

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

Re: Ranking position on a text box

Anonymous — Wed, 16 Dec 2015 17:26:40 GMT

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

Re: Ranking position on a text box

Anonymous — Mon, 21 Dec 2015 14:35:33 GMT

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?

Re: Ranking position on a text box

sunny_talwar — Mon, 21 Dec 2015 17:38:16 GMT

Try this:

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

Re: Ranking position on a text box

Anonymous — Tue, 22 Dec 2015 10:28:36 GMT

Perfect thanks

Re: Ranking position on a text box

Anonymous — Thu, 21 Jun 2018 11:38:15 GMT

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)