how to sort top 5 within Pivot table

[Anonymous]

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?

[sunny_talwar]

Can you provide a sample to play around with?

[Chanty4u]

=max((Group,5),vendor)

[Anonymous]

did you check this?

Calculating TOP 10 VALUES in PIVOT TABLE

Pivot Table top 10

how to display top 5 customers in pivot table

[Anonymous]

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

[Anonymous]

sunindia‌ ..?

[sunny_talwar]

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

[swuehl]

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]

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)