topic Re: Overall rank is changing based on selection in QlikView

Overall rank is changing based on selection

vamshi_1241 — Mon, 07 May 2018 09:17:01 GMT

Hi,

I have growth_rank, %growth_rank and PTQ _rank in my report. I had to show the overall rank (over these ranks) and I did and ranks are showing as expected but whenever we make a single selection on dimension,rank is showing 1 but I want their actual ranks after selection. Please let me know what I should do in this scenario.

Below is the logic I have written in expression:

aggr({1}Rank(

Aggr({1}Rank(growth_rank, 4,1),[Rep])

Aggr({1}Rank(%growth_rank,4,1),[Rep])

Aggr({1}Rank(PTQ_Rank ,4,1), [ Rep]),4,1),[Territory - Level 10 Rep])

Re: Overall rank is changing based on selection

sunny_talwar — Mon, 07 May 2018 11:40:33 GMT

Is this the same expression that we looked earlier or is this something new? I think that you need to not use the set analysis {1} in Aggr() but in Sum, Max, Min, Median, Concat, Avg,... etc to ignore selection....

Re: Overall rank is changing based on selection

sunny_talwar — Mon, 07 May 2018 11:42:45 GMT

May be like this

Aggr(

Rank(

Only({1} Aggr(Rank(Only({1} growth_rank), 4,1), [Rep]))+

Only({1} Aggr(Rank(Only({1} %growth_rank), 4,1), [Rep])) +

Only({1} Aggr(Rank(Only({1} PTQ_Rank), 4, 1), [Rep]))

, 4, 1)

, [Territory - Level 10 Rep])

Re: Overall rank is changing based on selection

vamshi_1241 — Mon, 07 May 2018 12:21:36 GMT

Yes Sunny, this is the same expression we looked earlier.

Re: Overall rank is changing based on selection

sunny_talwar — Mon, 07 May 2018 12:22:38 GMT

So, growth_rank, %growth_rank, and PTQ_Rank are all variables?

Re: Overall rank is changing based on selection

vamshi_1241 — Mon, 07 May 2018 12:26:00 GMT

Yes

Re: Overall rank is changing based on selection

sunny_talwar — Mon, 07 May 2018 12:29:51 GMT

Not sure what all things we tried, but can you try this?

Aggr(

Rank(

Only({1} Aggr(Rank(Only({1} growth_rank), 4,1), [Rep]))+

Only({1} Aggr(Rank(Only({1} %growth_rank), 4,1), [Rep])) +

Only({1} Aggr(Rank(Only({1} PTQ_Rank), 4, 1), [Rep]))

, 4, 1)

, [Territory - Level 10 Rep])

Re: Overall rank is changing based on selection

vamshi_1241 — Tue, 08 May 2018 12:11:46 GMT

Thanks Sunny, it is working now but few fields are showing '-' due multiple values. How can we fix this?

Re: Overall rank is changing based on selection

sunny_talwar — Tue, 08 May 2018 12:13:17 GMT

Can you share a sample to show the issue? I won't know until I see.

Re: Overall rank is changing based on selection

vamshi_1241 — Wed, 09 May 2018 10:36:42 GMT

Resolved by following expression.

Aggr(

Rank(

Only({1} Aggr(Rank(Only($(growth_rank)), 4,1), [Rep]))+

Only({1} Aggr(Rank(Only($(%growth_rank)), 4,1), [Rep])) +

Only({1} Aggr(Rank(Only($(PTQ_Rank)), 4, 1), [Rep]))

, 4, 1)

, [Rep])

Re: Overall rank is changing based on selection

sunny_talwar — Wed, 09 May 2018 11:09:22 GMT

Super