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.

       

      Capture13.PNG

       

      And sort tab of my pivot table like below

       

      Capture14.PNGCapture15.PNGCapture16.PNG

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

       

       

      Regards,

      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:

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

          ];

           

          4. Set the sort expression in the pivot table:

          step4.png

           

          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!

          Rich