9 Replies Latest reply: Dec 11, 2017 1:40 AM by Luis Madriz RSS

    Transpose Table

    ra ta

      Hi, I am displaying a table looking like the following:

       

       

      MaturitySales product1Sales product2Sales product3
      1 year5,9138,133-2,223
      2 year39,52538,560929
      3 year41,09439,8721,188
      4 year97,90479,5164,737
      5 year309,085290,6129,810
      6 year85,371109,4812,710

       

       

      That I would like to transpose to look like this:

       

          

      Maturity1 year2 year3 year4 year5 year6 year
      Sales product15,91339,52541,09497,904309,08585,371
      Sales product28,13338,56039,87279,516290,612109,481
      Sales product3-2,2239291,1884,7379,8102,710

       

       

      Any suggestions to do this please. Thanks very much.

        • Re: Transpose Table
          omar bensalem

          Create a pivot table:

          Capture.PNG

           

          as a dimension:

          =ValueList('Sales Product1','Sales Product2','Sales Product3')

           

          as a measure:

          if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product1', sum([Sales product1]),

          if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product2', sum([Sales product2]),

          if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product3', sum([Sales product3]))))

           

          add a line:

          Maturity

           

          RESULT:

          Capture.PNG

          • Re: Transpose Table
            ra ta

            Thanks very much Omar. if i understood correctly, i go to charts, and select Pivot table

             

            Capture.PNG

            open it and fill it using the code you suggested by adding a dimension

             

            Capture11.PNG

            • Re: Transpose Table
              Anil Babu

              Script / Front end?

              • Re: Transpose Table
                kaan erisen

                Hi,

                 

                You can achieve that by just converting your table to pivot table and switch column and rows. That's all you need

                 

                Untitled.png

                  • Re: Transpose Table
                    omar bensalem

                    Never thought this was  possible ! Thanks a lot !

                    • Re: Transpose Table
                      ra ta

                      Thanks Kaan. Can you please explain how to convert an existing table to pivot table ?

                      I have tried to create a new pivot table and flipped the columns into rows but did not get the right results. the reason being that the Sales Product columns don’t exist already in the tables, but I create them on the fly using formulas like

                       

                      =sum(if([Product]='Product1', [Sales])) for Sales product1 column.

                      =sum(if([Product]='Product2', [Sales])) for Sales product2 column

                      =sum(if([Product]='Product3', [Sales])) for Sales product3 column

                        • Re: Transpose Table
                          Luis Madriz

                          Hi,

                           

                          You can just drag a Pivot Table over a Table as select Convert if you want to convert it but you may have to review the default design and may not get exactly what you want right away.

                           

                          To create from scratch just try something simpler:

                          Dimension: Product

                          Measure: Sales and select Sum

                          Then Add Data (Column) and select Year

                           

                          Then, if you want, you can restrict Sales to specific products by editing the Measure and changing it from

                          Sum(Sales)

                          to

                          Sum({<Product={"Product1","Product2","Product3"}>} Sales)

                           

                          I hope this helps,

                           

                          Luis

                      • Re: Transpose Table
                        Saniya Shaikh

                        Hi,

                         

                        As Kaan rightly suggested, you can do it by simply creating a pivot chart and transposing your measures and Dimension.

                         

                        But an alternate way to do this is in the script as well. PFA images for your reference.

                         

                         

                        Scripting :-

                         

                        Output:-

                         

                        Regards,

                        Saniya.