topic Re: Get Fixed Rank Regardless selection in QlikView

Get Fixed Rank Regardless selection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good morning people,

I've been facing a problem solving a fixed rank.

So I hope you can help me

I've attached an example so it will be simpler for you to understand my question.

I have 3 fields: Cod, Val1 and Val2.

In table Rank, I have the sum of val1 and val2 for each cod. And I have the rank of that sum for each val.

Example:

In the last two columns I have the sum of ranks of val1 and val2 for each cod, and the rank of this sum.

Example:

Cod A is 4th in Rank of Val1 and 2nd in Rank of val2, so, the sum of ranks is 4+2 = 6.

My idea is, the cod which has the less number of Total Ranks Sum is the best cod.

Which in this case gives me that cod B is the best cod.

So far so good?

Finally here comes my problem:

I want that, when I select a single cod, A, for example, the rank doesn´t change, it stays the same.

Example:

I've choose A and rank of Val1 and Rank of Val2 stayed the same:

But I can't find the way to keep "Rank of Total Ranks Sum" the same too.

Basically I want that when I choose A, "Rank of Total Ranks Sum" gives me value 3.

Can you please help me ?

Thanks in advanced,

Sílvia

Re: Get Fixed Rank Regardless selection

Anil_Babu_Samineni — Thu, 17 Nov 2016 10:39:13 GMT

This is ok for you

Re: Get Fixed Rank Regardless selection

kaushiknsolanki — Thu, 17 Nov 2016 10:47:09 GMT

Hi,

Try this expression.

Aggr(Rank( Sum({1}aggr(rank(sum({<Cod=>} Val1),0,1),Cod)+aggr(rank(sum({<Cod=>} Val2),0,1),Cod))*-1),Cod)

Regards,

Kaushik Solanki

Re: Get Fixed Rank Regardless selection

Anonymous — Thu, 17 Nov 2016 10:55:22 GMT

Hi Anil,

Sorry, but I guess I didn't get what your saying.

I want that when I select cod A, column "Rank of Total Ranks Sum", gives me the value 3.

So in Fixed Rank table should appear:

Thanks,

Sílvia

Re: Get Fixed Rank Regardless selection

kaushiknsolanki — Thu, 17 Nov 2016 10:58:26 GMT

Hi,

Did you try the expression I gave.

Regards,

Kaushik Solanki

Re: Get Fixed Rank Regardless selection

trdandamudi — Thu, 17 Nov 2016 11:02:41 GMT

Please use the solution that Kaushik provided and it will give you 3.

Re: Get Fixed Rank Regardless selection

Anil_Babu_Samineni — Thu, 17 Nov 2016 11:07:05 GMT

Yes, The problem with Aggregation. Here, you've using without any filter aggregation flag. You may required Sum({1}...)

As Kaushik shared, It suits same as you expect

Re: Get Fixed Rank Regardless selection

Anonymous — Thu, 17 Nov 2016 11:17:44 GMT

Hi !Kaushik,

I did try your expression, but is still not giving me 3.

I've attached the sample app.

Am I missing something?

Thanks,

Sílvia

Re: Get Fixed Rank Regardless selection

trdandamudi — Thu, 17 Nov 2016 11:20:54 GMT

Please resize the last column and you will see 3. I just tried and it is working....

Re: Get Fixed Rank Regardless selection

Anonymous — Thu, 17 Nov 2016 11:22:41 GMT

I told you I was missing something

Thanks a lot