Diagram: Pivot sort

[#1]

Athough having searched to forum I didn't found a solution to my problem:

In a pivot diagram, I'd like to sort the rows first by a dimension (e.g. Country) and then by formula (e.g. Sales per Country)

Unfortunately, the ranking in is not correct - see attached example.

Any proposal would be most appreciated.

[kji]

Sort order is determined over the whole data set, thus you can't have the order X,Z,Y for A, and then a different ordering for these values under B.

[#3]

I hope it is only tricky, but not impossible, to sort the pivot by value, even though the dimensions order changes.

[johnw]

Well, yes, I suppose we could get tricky. For instance, we could use the dual() function to assign different numbers to the same text values for Product. Then they aren't forced to sort in the same order, because QlikView doesn't see them as the same product. Then sort by sum(Sales), and you can have different orders. It kind of breaks the pivot, because if you move your new product in front of Country, for instance, the products will repeat instead of combine. And if they select the product from the pivot table, they won't really be selecting the product, and it will narrow down the country as well. But that's probably less important to you than getting the right sort order. You can at least turn of pivoting.

Attached is an example of doing exactly that.

It's certainly possible that there's a better/easier approach. That's just the one that occurred to me.

[#5]

Hi John,

I've tried your example and am confused by the result.

I removed the sort by "sum(aggr(sum(Sales),Land,Produkt)" on the country in the second table and it still sorts it differently that the table above it. i've gone through all the settings and the 2 tables are vitually identical now.. it's like it hasn't recalculated?

[#6]

Sorry John, I realised you'd used the Dual() function to achieve the sort. my question is:

This works for a small set of countries and products but what happens when you have a dynamically changing list of 10,000 products and X number of countries?