Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to sort top 5 within Pivot table

GroupVendorID Counts
A146
A213
A3200
A412
A52

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe use a $Sort expression like

Pick(Dimensionality(),

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

-Column(2)

)

View solution in original post

12 Replies
sunny_talwar

Can you provide a sample to play around with?

Chanty4u
MVP
MVP

=max((Group,5),vendor)

Anonymous
Not applicable
Author

Anonymous
Not applicable
Author

@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.

rank_sort.PNG

Anonymous
Not applicable
Author

sunindia‌ ..?

sunny_talwar

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

swuehl
MVP
MVP

Are you looking for something like this?

sunny_talwar

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?

swuehl
MVP
MVP

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)