Double sorting in Pivot Table

[#1]

Hi,

I would like to sort two dimensions with expression in a pivot table.

I've 3 dimensions : Country, Product, Year and 1 expression Sum(Sales).

Country I Product I Year 2010 I Year 2009.

I would like to sort Country by Year 2010 (sum({<Year={2010}>] and sort Product by Year2010.

The sort by country is Ok but it doesn't sort by Product.

Can you help me ?

[johnw]

Karl Pover wrote:You can do it if you make an expression for each year and sort by the y-value like the attached example.

Huh. I would not have expected that to fix the problem. I'll need to keep that in mind.

[#3]

Karl, i'll use your solution.

Thanks to all of you.

[#4]

John,

Am I wrong or is this not also true of straight tables?

I posted my problem earlier and Nick Bor directed me to this post.

This is a basic list function that really needs to be supported. How then can we rank major dimension groups and the rank minor dimension groups within the major group?

Rich

[johnw]

Richard DeRocco wrote:Am I wrong or is this not also true of straight tables?

If I understand your question, no, it's not true of straight tables. To demonstrate this to yourself, just pull up the testExpressionSort.qvw that Nick posted. Convert the top chart into a straight table. Double click on Sales, then double click on Country, then double click on year. The products are now sorted differently within the context of the other dimensions.

With the pivot table, you can only sort by the dimensions, not by dimensions OR expressions. You CAN enter an expression to sort by, but that expression is always evaluated globally. There is no option to only evaluate that expression in context.

This is making me think, though, that the solution might be simpler than a "sort in context?" checkbox or the like. Now it seems like all we need to do is use the straight table version of sorting. In other words, put the expressions on the sort tab too, not just the dimensions. I haven't thought that through. They may be excluded for something having to do with our ability to put dimensions on either the left or on top. Not sure.

[#6]

Actually John the problem that lead me here was a bit different:

I'm working on a complex chart involving multiple dimensions and sorting within those dimensions. I was getting strange sequence results.

So I did a test on a very simple chart and was able to duplicate the problem in a simple pivot table with two dimensions and one expression -- sum (Sales).

I'm sorting dimension 1 using the expression -- SUM (TOTAL <DIMENSION1> Sales) works fine

I've tried several different expressions for the sort of dimension 2 including --SUM(TOTAL <DIMENSION1, DIMENSION2> (Sales) yielding the same results but for simplicity I'll stick with -- SUM (Sales) for the expression sort of Dimension 2.

In short I'm trying to sort the chart first by the decending value of sales for dimension 1 and then sorting Dimension 2 decending within the sorted dimension 1

When I make no selection in Dimension1 occasionally some of the rows of dimension 2 are out of the decending value sequence within Dimension1. If I make a selection of dimension 1 the results are correctly sorted for dimension 2. I've also gone back and changed my chart to a straight table with the same results. I can't get my head around why that is.