1 Reply Latest reply: Jan 29, 2013 12:04 PM by Rich Hemmings RSS

    How to sort the pivot table?

    Kabilan Kumarasamy

      Hi All,


      I have a pivot table.


      but I am not able to Sort in that pivot table.


      My Pivot table like below.




      And sort tab of my pivot table like below



      I have like this but Sorting is not worked in my pivot table.


      Is it possible to sort in the pivot table?


      I have three dimension , that's why I have a problem?


      please help me.


      Thanks in advance....




      Kabilan K.

        • Re: How to sort the pivot table?

          The Goal:

          Sort expression columns in a pivot table


          The Method:

          1. Create variables for the expressions you want to sort by

          LET vEqProjectBaseline = 'Sum([BackingSheetHours]) /7.5';
          LET vEqEstimate = 'Sum(Aggr([Estimated Hours], ProjectTaskKey)) /7.5';
          LET vEqBaselineVsEstimate = '($(vEqEstimate)) / ($(vEqProjectBaseline))';

               N.B – brackets and apostrophes are a real minefield


          2. Create function to evaluate the variables inline

          SET fxEval = Evaluate($1);


          3. Create the custom sort table:

          LOAD %DisplayValue
          $(fxEval(%ExVariable)) as %SortingExpression
          %DisplayValue ,%ExVariable
          Project Baseline ,vEqProjectBaseline
          Estimate ,vEqEstimate
          Baseline vs Estimate      ,vEqBaselineVsEstimate



          4. Set the sort expression in the pivot table:



          5. Create a selector for the sorting options (I used %DisplayValue in this example)



          The Goods:

          Adds sorting to pivot tables

          Doesn’t seem to affect performance (not yet tested on large datasets)


          The Bads:

          This will probably be a bummer to maintain (– I’m thinking mismatching expressions between the table and the variables)

          Takes a couple of hours to set up and test

          You'll only be able to sort ASC [OR] DESC, not have the option of both



          Hope that helps!