topic Re: how to sort top 5 within Pivot table in QlikView

how to sort top 5 within Pivot table

Anonymous — Thu, 25 Feb 2016 00:28:53 GMT

Group	Vendor	ID Counts
A	1	46
A	2	13
A	3	200
A	4	12
A	5	2

I have a pivot table where I am counting IDs (3rd column above) for top 5 vendors grouping by Group. Within Groups, only A is shown here, I want the ID Counts to sort in descending order. So in the table above it sud be in 200,46,13,12,2 order.

I am using the following expn to count IDs: sum(aggr(if((rank(count({ fieldA = {1}>} ID))) <=5,count({<FieldA = {1}>} ID)),Group, Vendor))

How do I make it to sort within the group with max count at the top?

Re: how to sort top 5 within Pivot table

sunny_talwar — Thu, 25 Feb 2016 00:32:02 GMT

Can you provide a sample to play around with?

Re: how to sort top 5 within Pivot table

Chanty4u — Thu, 25 Feb 2016 05:13:30 GMT

=max((Group,5),vendor)

Re: how to sort top 5 within Pivot table

Anonymous — Thu, 25 Feb 2016 05:22:25 GMT

did you check this?

Calculating TOP 10 VALUES in PIVOT TABLE

Pivot Table top 10

how to display top 5 customers in pivot table

Re: how to sort top 5 within Pivot table

Anonymous — Mon, 07 Mar 2016 19:53:09 GMT

@SunnY T,

I have attached the qvw. I want to sort first by A-Z Group, then within group I want the vendor with max count at the top.

for EG. for Group A, it shoule be Vendor 1,2,3,4,5 in that order.

Re: how to sort top 5 within Pivot table

Anonymous — Mon, 07 Mar 2016 21:30:46 GMT

sunindia‌ ..?

Re: how to sort top 5 within Pivot table

sunny_talwar — Mon, 07 Mar 2016 21:32:41 GMT

No man, I have run out of options. May be swuehl‌ can help you out

Re: how to sort top 5 within Pivot table

swuehl — Tue, 08 Mar 2016 01:02:12 GMT

Are you looking for something like this?

Re: how to sort top 5 within Pivot table

sunny_talwar — Tue, 08 Mar 2016 01:24:22 GMT

Stefan would you be able to elaborate what you did? Why is $Sort expression not visible in the pivot table? Is there a way to hide an expression from Pivot table?

Re: how to sort top 5 within Pivot table

swuehl — Tue, 08 Mar 2016 01:35:23 GMT

I think the sample chart could be just sorted by y-value desc to return the requested result. But that's just because the first dimension should be sorted A-Z and this by accident matches sort order by y-value.

To explicitely sort first dimension A-Z and second by v-value desc, set the sort order to y-value asc and create the first expression like

Pick(Dimensionality(),

[$(=GetObjectField(0))],

-Column(2)

)

You can't hide the first expression column, but you can set the column width to zero. There is a macro in my sample app that does this (only need to be executed once during development).

It's all described here (kudos! cschwarz‌)

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

Re: how to sort top 5 within Pivot table

Anonymous — Tue, 08 Mar 2016 02:13:17 GMT

Thank you sunindia‌ and swuehl‌ for helping out. I will implement these in my app and update.

Re: how to sort top 5 within Pivot table

Anonymous — Tue, 08 Mar 2016 13:56:25 GMT

Swuehl,

It works almost perfectly. But my actual app has one more condition; the order of the Group is not "Normal".

from the image below: The user wants the sort order to be B -101, A -102, D -103, C -104. How to make this possible.

I thought we had this under control. Thanks for your help again.

Re: how to sort top 5 within Pivot table

swuehl — Tue, 08 Mar 2016 14:17:11 GMT

Maybe use a $Sort expression like

Pick(Dimensionality(),

Subfield([$(=GetObjectField(0))],'-',2),

-Column(2)

)