topic getting Rankings from 2 Pivot Tables in QlikView

getting Rankings from 2 Pivot Tables

cafcptg2011 — Tue, 17 Mar 2015 18:24:54 GMT

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

Re: getting Rankings from 2 Pivot Tables

JonnyPoole — Tue, 17 Mar 2015 23:12:51 GMT

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 ?

Re: getting Rankings from 2 Pivot Tables

cafcptg2011 — Wed, 18 Mar 2015 10:14:26 GMT

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

Re: getting Rankings from 2 Pivot Tables

JonnyPoole — Wed, 18 Mar 2015 13:27:46 GMT

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 ))

Re: getting Rankings from 2 Pivot Tables

cafcptg2011 — Wed, 18 Mar 2015 17:13:19 GMT

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

Re: getting Rankings from 2 Pivot Tables

JonnyPoole — Thu, 19 Mar 2015 01:09:49 GMT

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

Re: getting Rankings from 2 Pivot Tables

cafcptg2011 — Thu, 19 Mar 2015 14:31:15 GMT

hi,

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

again thanks very much for your kind help

CAFC