I was asked the following question privately, but I'd like to address it publicly, and see if others have a different understanding of how this currently works, or different opinions on the requested enhancement.
"I've searched high and low through the forums for sorting pivot table dimensions by expression without any luck.
Simple Really in theory. 3 dimensions (Brand, Range, and Sales Rep). 1 Dimension (Sum(Value)). if all sort flags are unticked except for the expression on [Sales Rep] which is =Sum(Value) it actually sorts the Sales reps by their total $$ irrespective of the dimensions. Is this a bug or something you're aware of?"
To start with, no, I don't think it's a bug. I believe it is working as designed. I just think the design could use some work.
Say we have dimensions Brand, Range and Sales Rep. Brand and Range are sorted in whatever way, say alphabetically, while Sales Rep is sorted by expression sum(Value). What we see is something like this:
Brand Range Sales Rep sum(Value)
A E Bob 10
A E Carla 20
A F Bob 12
A F Carla 5
The "problem" is with the last two lines. Bob comes before Carla, even though Bob has a higher sum(Value). In this example, we would like, instead, for Bob and Carla to be sorted in context, so for these two rows, in the context of Brand A and Range F. Sounds logical enough:
Brand Range Sales Rep sum(Value)
A E Bob 10
A E Carla 20
A F Carla 5
A F Bob 12
But then what happens if you drag the Sales Rep to the top of the chart? Now you can no longer sort in the context of the other dimensions, because it would be different for each row, which is now impossible. The sort order must now be consistent across the rows. So in that case, it seems like it MUST interpret sum(Value) in the context of the whole chart, not by row.
Brand Range Sales Rep Bob Carla
A E 10 20
A F 12 5
And here's where I think QlikView's design is lacking - rather than distinguish between these two cases, it appears that they chose to ALWAYS sort Sales Rep the same way, regardless of WHERE you put it in the chart. First dimension, last dimension, to the left of the data, on top of the data, they don't appear to care. It appears that what QlikTech thought was most valuable here was CONSISTENCY - always showing the data in the same order.
Fair enough, I suppose. Lots of people might want their charts to behave that way, so it should at least be an option. But I think QlikView needs another option. I think we should have the option to sort a pivot table dimension IN CONTEXT of any dimensions to the left of it (if it is to the left of the chart), or above it (if it is above the chart).
If I've understood the problem correctly, fixing it was proposed earlier today in the customer portal:
https://emea.salesforce.com/ideas/viewIdea.apexp?id=08720000000HLlc&srPos=1&srKp=087
And was also proposed last year:
https://emea.salesforce.com/ideas/viewIdea.apexp?id=087200000008T02&srPos=15&srKp=087
In the earlier proposal, there appears to be this response from QlikTech:
"I tried to replicate what I believe you are seeing in the attached QVW. I have created a sort expression on the second dimension in the pivot table. As you can see it does sort correctly by sum(sales) of Sales Rep 2. I am not sure if you are testing or seeing something else. Please send an example of the problem you have encountered."
So there is reason to think that perhaps this behavior is inconsistent, though that seems less likely than simple misunderstandings. The mentioned attachment appears to have been lost in the migration to the new forum, so I can't check it for myself.
Attached is my own example, showing the sort occurring globally rather than in context. I'm using QV9 SR5.
Thoughts?
