topic Re: aggr rank pivot table in QlikView

aggr rank pivot table

Tue, 08 Jul 2014 20:47:47 GMT

Hi,

I'm trying to use aggr and rank together in a pivot table to return a ranking relative to the two dimensions in my pivot table, Dim1 and Dim2. Here's what I'm using now:

=aggr(

rank(

Sum( {<[Hit Action] = {"Join Page"} >} _f_OK_SIGNUP)/Sum( {<[Hit Action] = {"Join Page"} >} [Visit Counter])

,0,1)

,[Dim1],[Dim2])

Without the aggr, it returns a rank overall all the records. I need to return a rank based on Dim1 and Dim2. Can anyone see the mistake I'm making?

Matt

Re: aggr rank pivot table

rwunderlich — Tue, 08 Jul 2014 21:23:18 GMT

Do you want the ranking to reflect the ranking of Dim2 within Dim1? Or the overall ranking of the Dim1/Dim2 value within all combinations? I'm going to guess overall ranking.

If Dim1 and Dim2 are the only dimensions in your table, then I think you just need: (I've omitted the SA for convenience, add it back in).

rank(Sum(TOTAL [Visit Counter]))

No aggr needed. If there are other Dimensions in your chart, you will need the aggr().

aggr(NODISTINCT

rank(TOTAL sum(Expression1))

,Dim1, Dim2)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: aggr rank pivot table

Tue, 08 Jul 2014 22:23:35 GMT

Hi Rob,

Thanks for the reply. Regarding your question, I'd like the to reflect the ranking of Dim2 within Dim1.

Will review and test your expressions shortly and get back to the thread. If you have anything else to add re the raning of Dim2 within Dim1 please do.

Thanks,

Matt

Re: aggr rank pivot table

rwunderlich — Tue, 08 Jul 2014 22:31:38 GMT

Dim2 with Dim1. Same answers above except remove the TOTAL qualifier.

-Rob

Re: aggr rank pivot table

Wed, 09 Jul 2014 13:37:07 GMT

Hi Rob,

Thanks for the help. My question wasn't clear, apologies for that. I'm using the following SA to return the number 1 ranked Template ID based on each template's conversion ratio [Sum( _f_OK_SIGNUP)/Sum( [Visit Counter])]:

=only({<[Template ID]={'=rank(Sum( _f_OK_SIGNUP)/Sum( [Visit Counter]),0,1)=1'}>} TOTAL [Template ID])

However, when I use the SA in a pivot table with two dimensions, Merchant and Country, it's returning the overall best template and I need it to return the best template per merchant. If I make a selection on merchant, the right template is returned, however I need it to work without selecting the merchant and am unclear as how to do this.

Sorry again for not being clearer in the beginning. Hopefully you can point me in the right direction.

Matt

Re: aggr rank pivot table

rwunderlich — Wed, 09 Jul 2014 18:22:04 GMT

Ohhh...that's completely different!.

Set Analysis is evaluated only once for the chart. It is dimensionless. You will need to use an if() instead. Something like:

if( rank(TOTAL<Country> Sum( _f_OK_SIGNUP)/Sum( [Visit Counter]),0,1)=1, [Template ID])

Not totally tested, but you get the idea.

-Rob