1 Reply Latest reply: Feb 28, 2013 3:48 AM by Gysbert Wassenaar RSS

    Sorting a pivot table by an expression

    Tom Cotterill

      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 1st 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.