topic Re: Pivot table sorting issue in QlikView

Pivot table sorting issue

dandaanilreddy — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Pivot table sorting issue

sunny_talwar — Wed, 02 Nov 2016 02:41:23 GMT

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

Re: Pivot table sorting issue

Anonymous — Wed, 02 Nov 2016 12:23:25 GMT

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.

Re: Pivot table sorting issue

dandaanilreddy — Wed, 02 Nov 2016 16:42:05 GMT

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

Thanks

Anil

Re: Pivot table sorting issue

Anonymous — Wed, 02 Nov 2016 16:53:34 GMT

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

Re: Pivot table sorting issue

MK9885 — Wed, 02 Nov 2016 17:36:25 GMT

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.

Re: Pivot table sorting issue

dandaanilreddy — Wed, 02 Nov 2016 17:38:28 GMT

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

Thanks

Anil

Re: Pivot table sorting issue

MK9885 — Wed, 02 Nov 2016 17:43:38 GMT

Can you upload a sample qvw if possible?

Re: Pivot table sorting issue

dandaanilreddy — Wed, 02 Nov 2016 17:55:52 GMT

Arvind

I have attached the sample data on the main discussion.

Thanks

Anil

Re: Pivot table sorting issue

MK9885 — Wed, 02 Nov 2016 18:14:55 GMT

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

Re: Pivot table sorting issue

dandaanilreddy — Wed, 02 Nov 2016 20:14:02 GMT

Thanks Arvind. It's working.

Thanks

Anil