Multi Dimensional Sort in Pivot Table

[#1]

I am trying to implement a multi-dimension sort order in a Pivot Table. I am trying to come up with a way to sort the top first dimension values, and within those values, sort the second dimension values. In real world terms, I have an example with States, Cars, and the number of Registrations of those cars in each State. For example, I have 20 Toyotas registered in CT, and that is the most of any car brand in that State. In NY, however, the most popular car is Chevy, so that should be first. Depending on which method I try to use, I get different results, but no method works for each row.

I can get a Pivot Table to sort very nicely if the data is in a true hierarchy. Unfortunately, this is not the case with the data I need to display.

Thanks for any suggestions.

Phil

[stephencredmond]

Hi Phil,

The problem is that the Pivot table sorts by the total values. If you pivot your state to be horizontal and add partial sums, you can see the "correct" sort order of TOYOTA, CHEVY, DODGE, VW, MAXDA, BMW (should be MAZDA???).

Your attempt to use aggr is no use because it will not evaluate at the total level that QlikView is using - you will see the same sort if you remove the expression option altogether.

Sorry

Stephen

[#3 · Accepted solution]

I discovered the solution to my problem.

For the first dimension, use Sort by Y Value. The second dimension, I just used the default order for that field/group.

The trick is that you MUST Show Partial Sums against the second dimension. This will create and keep the order of the first dimension in tact, regardless of Expand/Collapse state. You can even allow Pivoting as long as you Show Partial Sums against the first dimension as well.

Thanks to all we looked at this.

Phil Bishop

[Anonymous]

Hi Phil,
(And welcome back )
Would you mind to post your solution on Wiki or "Share"? I posted "dynamic sorting in pivot tables"
recently, and your solution is on the similar topic.