4 Replies Latest reply: Mar 16, 2012 9:52 AM by Cyril Lamaison RSS

    PivotTable ordered multiple sizes

      hello, I would like to know how can I sort a pivot table
      divided into 2 blow up product ID, Warehouse and order in descending order by sum (Sales
      thanks

      exsemple

      Product ID           Warehouse              sum(Sales)

      A                                   001                                   20

                                           002                                   09

      B                                   001                                   15

                                           002                                    10

                                           003                                    09

        • PivotTable ordered multiple sizes
          Oleg Troyansky

          Sorting pivot tables is tricky... You can sort each one of the Dimensions by an expression (use tab "Sort"), but keep in mind, that each Dimnsion will be sorted individually, without any regard to other dimensions. For example, in your case, if Warehouse 3 has more Sales Associated with it, it will appear on top, even for those Products where it's portion is not the highest.

           

          In order to enforse sorting by Product and then Warehouse, you can try using Advanced Aggregation AGGR(), listing all the dimensions - this way, you might be able to sort it properly...

           

          cheers,

           

          Oleg

          • PivotTable ordered multiple sizes
            Anand Chouhan

            Hi,

             

            You need to sort by column orders like go to properties -> Sort -> product ID ascending order then Warehouse ascending order and then click on expression and type =sum(Sales) put ascending order to be selected and see the resut do you have any sample if so please provide me.

             

            Regards,

            Anand

            • PivotTable ordered multiple sizes
              Stefan Wühl

              Hi Massimo,

               

              as already stated, sorting a pivot is kind of tricky (and I just noticed results are even depending if you are using partial sum on first, second or both dimensions).

               

              It may be useful if you could describe, what your expected result of sorting should look like, e.g. I assume that you don't want something like:

               

              Product ID           Warehouse              sum(Sales)

              A                                   001                                   20

              B                                   001                                   15

              B                                   002                                    10

              A                                   002                                   09

              B                                   003                                    09

               

              (I think you can't do that with a pivot anyway, use a straight table instead).

               

              If you just want to sort first dimension by desc sum, then secon dimension, I got that by using sort by y value on first dimension, adding partial sums. You may want to play around what is best for your needs.

               

              Stefan

               

              D