Pivot table sorting issue

[dandaanilreddy]

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

[sunny_talwar]

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

[Anonymous]

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]

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

Thanks

Anil

[Anonymous]

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]

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]

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

Thanks

Anil

[MK9885]

Can you upload a sample qvw if possible?

[dandaanilreddy]

Arvind

I have attached the sample data on the main discussion.

Thanks

Anil

[MK9885 · Accepted solution]

Thanks
You want highest to lowest Sum(Amount) value for all dimensions?