topic Re: Ranking within a pivot table in QlikView

Ranking within a pivot table

tmumaw — Mon, 25 Jan 2016 14:42:11 GMT

Hello All,

I have a small problem and I know that I am not the only one that has been presented with this. I have a pivot table which is made up of sales people and services sold by year. I am trying to rank on 2015 and 2014 all services by sales person. I have tried everything. When I select only a sales person it ranks fine, but when I have multiple sales people the ranking is all over the place. My dimensions are Salesperson, services, Year summing sales amount. Here are the expressions:

Rank: rank(sum(Inv Sales))

Sales: sum(Inv Sales)

Sort : Services : aggr(sum({<FISYR = {$(=$(vFisYr) - 1)}>} [Inv Gross]),[Material - Code & Name]) decending....

Thanks

Re: Ranking within a pivot table

Oleg_Troyansky — Mon, 25 Jan 2016 15:07:23 GMT

Hi Thom,

I think you should add a TOTAL keyword and play with the combination of the TOTAL dimensions. In a Pivot table, the ranking applies to the complete set of chart dimensions - in your case, Salesperson, Services, and Year. Perhaps you need the TOTAL by Salesperson? Or by Salesperson and Year?

Are you planning to come back to one of our Master Summit sessions? We are changing the content quite a bit this year!

cheers,

Oleg Troyansky

Re: Ranking within a pivot table

tmumaw — Mon, 25 Jan 2016 16:19:29 GMT

Hi Oleg,

Yes I do want to try and make it back to one of the Master Summits, but trying to get away lately has been pretty difficult. Thanks for the response. Did you mean try the total on the rank or the sum, or within the sort?

Thom

Re: Ranking within a pivot table

Oleg_Troyansky — Mon, 25 Jan 2016 18:59:35 GMT

I meant the total in the Rank()

Re: Ranking within a pivot table

tmumaw — Mon, 25 Jan 2016 19:52:49 GMT

It appears there is no way to rank a pivot table the way I want to (at least I have not figured it out). I have a salesperson, services and I want to rank the dollar value for 2014 and 2015 by service by arborist. what I would like to see is : Every time the arborist changes I need to rank total sales. Thanks Oleg.....

Re: Ranking within a pivot table

Oleg_Troyansky — Tue, 26 Jan 2016 14:37:33 GMT

Hi Thom,

if you posted a small sample app, I could give it a try...

Oleg

Re: Ranking within a pivot table

tmumaw — Tue, 26 Jan 2016 14:46:54 GMT

I can email you a spreadsheet if that works? Thanks for all your help.

Thom

Re: Ranking within a pivot table

tmumaw — Tue, 26 Jan 2016 15:13:21 GMT

Here is a small sample. Hope it helps. Thanks Thom

Re: Ranking within a pivot table

Oleg_Troyansky — Tue, 26 Jan 2016 16:13:40 GMT

Thom,

I can see that the TOTAL <dim> doesn't work in the Rank function - the helper popup text shows it, but it doesn't work. The general TOTAL works - you can calculate RANK across all Arborists and Services.

I'm not sure what's your desired result though. Can you attach a working example in Excel?

Oleg

Re: Ranking within a pivot table

Oleg_Troyansky — Tue, 26 Jan 2016 16:15:15 GMT

Attaching the document that I have so far ...

Re: Ranking within a pivot table

tmumaw — Tue, 26 Jan 2016 16:30:33 GMT

Maybe we look at this a different way. Is there a way to tell me who the number one arborist is for a service and ignore the rest?

Thanks

Thom

Re: Ranking within a pivot table

Oleg_Troyansky — Tue, 26 Jan 2016 16:49:00 GMT

It sounds like you want to ignore Years... Something like this (attached)?

Re: Ranking within a pivot table

tmumaw — Tue, 26 Jan 2016 19:22:47 GMT

This might work.....I changed it to a straight table, removed the year and used the dimension limits......it shows me the top arborist for a service. What do you think?

Thanks

Thom

Re: Ranking within a pivot table

Oleg_Troyansky — Tue, 26 Jan 2016 20:37:04 GMT

Well, I didn't know all these changes were on the table 🙂 Sure, it works!

Re: Ranking within a pivot table

tmumaw — Tue, 26 Jan 2016 21:15:54 GMT

Thank you very much for all your help. Keep me posted on the Masters Summit.....Will you be at the QConference in May?

Thom

Re: Ranking within a pivot table

Oleg_Troyansky — Wed, 27 Jan 2016 14:35:25 GMT

Yes, absolutely!

Re: Ranking within a pivot table

tmumaw — Wed, 27 Jan 2016 15:06:01 GMT

Great. I will look for you I owe you for all your help,..

Thom