Sorting a pivot table by an expression

[thomastc]

Hello community,

I am sure this must have been asked before but I have been unable to find a solution to my problem.

I have a pivot table with 2 dimensions; one dimension is a Drill-Down group.

There are 3 expressions, 2 simple sums and the third is the variance of those 2 (a-b).

One dimension (the non-drill-down dim) is vertically on the left, the other dim goes across the top. There is a totals column on the left.

Dimensions:

Dim1 := Store

Dim2(Drill-Down): = Area & Sub-Area

Expressions:

Exp1:= Sum(Sales)

Exp2:= Sum(PlannedSales)

Exp3:= Exp1 – Exp2

They want to sort by the stores with the greates variance between sales and planned sales. I want to sort the 1^st dimension (store) by the total column of Exp3. I cannot get it to work. I can sort by Exp1 or Exp2 but not Exp1 – Exp2.

Also if anyone knows a good way of alternating the pivot table dimension BG colour if they could let me know that would be great!

Any tips or pointers on getting this to work or is it not posible?

Unfortunately I am unable to post my qvw file.

Thanks in advance.

[Gysbert_Wassenaar]

As far as I know you can only sort on the first expression with the sort by Y-value option. You could promote your third expression to first position. That said, sorting in pivot tables is finicky and rather limited.