Sorting multiple dimensions simultaneously in pivot table
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?