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