getting Rankings from 2 Pivot Tables

[cafcptg2011]

Hi, I have 2 pivot tables with the ranking of sales, so far so good, now when I click in one ranking position I need to know the same ranking region but from the other area.

I have this (and is ok):

Now, when I click for instance on ranking 1 (yellow) I get this (not ok):

What I need is this (after I have clicked on rank 1):

(of course without selecting regions f and a at the same time)

I have a qvw example to understand better my pb:

http://community.qlik.com/servlet/JiveServlet/downloadBody/8143-102-1-11188/getRank.qvw

Can some one help me, please?

thanks in advance,

CAFC

[JonnyPoole]

You may need SET ANALYSIS to disregard the Area selection in the 2nd ranking expression

sum(  {<Area=>}  Sales) ...disregards sales

Set analysis is only valid in chart aggregation functions however, are you using one with your rank() expression ?

[cafcptg2011]

hi,

thank you very much for your reply...

I have changed my 2nd ranking expression to your expression but unfortunately still doesn't work..

thanks

[JonnyPoole]

Change thjs:

=concat ( } IF( aggr ( Rank(sum(  { < Area ={$(=min(  Area))} >   }     Sales),4,1),Region)=1, Region ))

To:

=concat ( {<Area=>} IF( aggr ( Rank(sum(  { < Area ={$(=min( {1} Area))} >   }     Sales),4,1),Region)=1, Region ))

Two changes:

1. Add set modifier in concat() to cancel the Area selection

2. Add global set identifier {1} to ensure the Min Area is done on the global selection, not the user's selection. (you can modify this as well to the following if you want it to 'listen' to other selections besides Area like this

=concat ( {<Area=>} IF( aggr ( Rank(sum(  { < Area ={$(=min( {<Area=>} Area))} >   }     Sales),4,1),Region)=1, Region ))

[cafcptg2011]

Hi,

I have changed my expression with your expression but still doesn't work.

for instance if I click on rank 3

I will get this:

the correct result would be:

because I have clicked on ranking 3 so it should show regions with ranking 3...

my new qvw file with your expression is here:

http://community.qlik.com/servlet/JiveServlet/downloadBody/8145-102-1-11201/getRank2.qvw

thanks for your help.

cafc

[JonnyPoole]

Got it. It seems like a complicated ask but probably doable. If i get a chance i'll come back to this.

[cafcptg2011]

hi,

I really appreciate that because a I need this to be done.

again thanks very much for your kind help

CAFC