topic Re: Problem while implementing rank in QlikView

Problem while implementing rank

Fri, 29 Jul 2011 18:49:08 GMT

We have a requirement where a client needs to be ranked against different peer groups (type/tier)

I have attached an example do demonstrate the problem..

Once a client (CMU) is selected, user should be able to pick any different Type & Tier and the selected client should be ranked against clients falling under the selected Type and Tier.. I tried to use Qualify and designed below expression, but that does not seem to work properly...

RANK(sum({<TYPE=QUAL_CLIENTS.TYPE,TIER=QUAL_CLIENTS.TIER>}REVENUE),1,1)

business wants to find out how a client was performing in each month against the other set of clients...

Can someone please help in building the above expression.. also please provide if you have better solution where we dont need to use Qualify

thanks in advance!!

EDIT - Please refer to Test.qvw from 3rd post.

Re: Problem while implementing rank

kaushiknsolanki — Sat, 30 Jul 2011 08:27:34 GMT

Hi,

Have a look at the attached document.

Is these what you want..?

Regards,

Kaushik Solanki

Re: Problem while implementing rank

Sun, 31 Jul 2011 16:31:19 GMT

Thanks Kaushik..

I am not looking for straightforward ranking.. this is a little different requirement...

Attached file shows an example and the details as to what type of ranking I am looking for...

If I have to write a program, this is what I'd do -

- for selected tier and type, find all associated clients

- among those clients, rank the selected client in terms of revenune for each month.. even though the selected client does not fall under selected tier and type..

It seems challenging defining this logic using QlikView...

Re: Problem while implementing rank

johnw — Mon, 01 Aug 2011 19:18:29 GMT

So if I select GOLD and BANKS, the CMUs are AMUNDI, BRANDYWINE and TEMPLETON GLOBAL. You want to rank the selected CMU, SLS CAP, against these three other CMUs on total revenue regardless of type and tier. In this case, it should rank 3 in January and 2 in February and March. But you want to do that without having to select AMUNDI, BRANDYWINE, SLS CAP and TEMPLETON GLOBAL, and without deselecting TYPE and TIER.

If so, then you almost had it. I believe you only need to add a single character to your expression, a + sign to indicate a union. This produces the right results for the example, and I believe matches the above definition:

if(CLIENT_VAR=CMU,rank(sum({$+<CMU=,TYPE=QUAL_CLIENTS.TYPE,TIER=QUAL_CLIENTS.TIER>} REVENUE)))

Re: Problem while implementing rank

hdonald — Tue, 02 Aug 2011 17:04:33 GMT

Hi,

Trying a slight variation on that solution, to try and remove the 'qualified by' table bit, ended up with an expression like this....(amended doc attached)

=if(CLIENT_VAR=maxstring( {$+1<CMU={'$(CLIENT_VAR)'}>} CMU) ,

rank( sum({$+1<CMU={'$(CLIENT_VAR)'}>} REVENUE )))

which seemed to work with just selecting from a Client list (which could be made into a single field data island) - other Tier and Type selections could be done on the normal table fields.

However, I'm not sure if there isn't a simpler way of doing the 'If (CLIENT_VAR = CMU..' condition as I've had to add a 'maxstring' to pick out distinct values for the condition to work. Are there other alternatives ?

Regards,