Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Maybe use a $Sort expression like
Pick(Dimensionality(),
Subfield([$(=GetObjectField(0))],'-',2),
-Column(2)
)
Can you provide a sample to play around with?
=max((Group,5),vendor)
@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.
sunindia ..?
No man, I have run out of options. May be swuehl can help you out
Are you looking for something like this?
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?
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)