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: 
dandaanilreddy
Partner - Creator III
Partner - Creator III

Pivot table sorting issue

Hello Qlik Developers

I have a straight table with 6 dimensions and 1 expression i.e sum(amount). i have sorted the dimension based on expression and the result is fine. I converted the straight table to pivot table and i don't see the sorting. I tried by using sort by expression like rank(aggr(expression,dimension)) but it's not working. Please find the attached image. Can someone help me to get the result in pivot table same like in straight table.

Thanks

Anil

1 Solution

Accepted Solutions
MK9885
Master II
Master II

line.pngThanks
You want highest to lowest Sum(Amount) value for all dimensions?

View solution in original post

10 Replies
sunny_talwar

Have you tried sorting by Y-value descending within your pivot table?

Capture.PNG

Anonymous
Not applicable

It looks like it's still sorted by Amount, just like in your straight table. If you want to see the $500 and $300 amounts you'll have to expand the Region "Southern CA." The Chicago region just doesn't have as high of values for Amount.

dandaanilreddy
Partner - Creator III
Partner - Creator III
Author

Yes, i Tried but it's not displaying the data in descending order

Thanks

Anil

Anonymous
Not applicable

Since you have Area, Region, Territory, and CFS Name as pivotable dimensions there would be no other option than to sort by those dimensions first, in order. It's only within those dimensions that you can do further sorting. However, if you want to display, say, the Area with the highest Sum(Amount) before all of the other Area values, you can click "Promote" on Area under "Sort Priority" until it's at the top, and enter Sum(Amount) into the descending expression. Then you can promote Region to the second highest priority dimension. After sorting all of the dimensions you want in the order that you want, you can then sort by the unsummed Amount, which is what you have in your pivot table already. But note that the Area with the highest Sum(Amount) won't necessarily have the highest individual Amount, i.e. those values in the $500s and $300s might not occur in the Area or Region with the highest Sum(Amount).

MK9885
Master II
Master II

Use your rank(aggr(expression,dimension)) in Region, Area, Territory and CFS Name or whatever dimension you trying to sort and then Ascending.

I hope you'll get that value from highest to lowest.

As you'll drill down from dim to dim the sorting will change and if you give rank(aggr(expression,dimension)) for only Region then it will sort for Region but not others.


dandaanilreddy
Partner - Creator III
Partner - Creator III
Author

Thanks for your reply Arvind. I tried but no luck.

Thanks

Anil

MK9885
Master II
Master II

Can you upload a sample qvw if possible?

dandaanilreddy
Partner - Creator III
Partner - Creator III
Author

Arvind

I have attached the sample data on the main discussion.

Thanks

Anil

MK9885
Master II
Master II

line.pngThanks
You want highest to lowest Sum(Amount) value for all dimensions?