topic Multi Dimensional Sort in Pivot Table in QlikView

Multi Dimensional Sort in Pivot Table

Mon, 06 Jul 2009 06:20:36 GMT

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

Multi Dimensional Sort in Pivot Table

stephencredmond — Mon, 06 Jul 2009 17:37:42 GMT

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

Multi Dimensional Sort in Pivot Table

Mon, 06 Jul 2009 22:19:57 GMT

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

Multi Dimensional Sort in Pivot Table

Anonymous — Mon, 06 Jul 2009 23:16:04 GMT

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.