Sorting multiple dimensions simultaneously in pivot table

[#1]

I know similar topics have been discussed quite often, but I haven't been able to find a solution that works (if at all possible).

I am creating a pivot table with three dimensions, and I want all of them to be sorted by % notional. I also need subtotals, which is why I'm using a pivot and not a straight table. Unfortunately I cannot share any data for company policy, but I recreated a mock dataset to explain the issue.

I tried breaking down the problem into smaller parts. If I create a pivot table with just one dimension and sort using an expression in sort tab, that gets sorted properly. If I add a second dimension, then dimension 1 is still sorted correctly, however dimension 2 is sorted according to all its global values instead of just using the values belonging to each value of dimension 1. Same goes when I add dimension 3.

As of now, I ticked sort by expression, descending according to sum(notional) for all tables and dimensions. I tried alternative solutions, like creating an additional variable that groups the entries from fields 1, 2, 3, but that wasn't returning a solution esthetically good enough. In addition, it wasn't pivoting on fields 1, 2, which is a requirement here.

Am I trying to do something not possible in Qlikview? If not, how can this be resolved?

Thanks

[swuehl · Accepted solution]

Marco, you need to create a (hidden) expression column to sort by, have a look at

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

[#3]

Thank you Stefan. I had overlooked that post, it was as easy as ticking off all sort options and simply tick sort by y-value on the first dimension!