4 Replies Latest reply on Sep 20, 2019 10:02 AM by Hari Ankem

    Pivot a Data from Excel

    Shrey Jain

      Can we pivot below data in tableau from  excel in proper format.

       

       

      Sep-19Oct-19
      RegionTargetActualTargetActual
      North2437653434
      South343476534
      East77344577
      West56892356

       

      The data in tableau must show as:

       

      REGION     Month      Target   Actual

      North             Sept -19       243          765

      South             Sept -19        34         34

      East               Sept -19        77            34

      West              Sept -19        56            89

        • 1. Re: Pivot a Data from Excel
          Hari Ankem

          Since you have not shared your actual data file, I presume it's in this format:

           

          1.png

           

          You will need to clean it with the data interpreter and then go for the pivot as shown below and then proceed from there.

           

          1.png

           

          1.png

           

          Hope this helps.

          • 2. Re: Pivot a Data from Excel
            Manjula Muppavaram

            Hi Shrey Jain,

             

            Please find attached twbx file for reference, if it is useful please mark as correct.

             

            regards,

            Manjula

            • 3. Re: Pivot a Data from Excel
              Shrey Jain

              Hi Hari,

               

              Year-month field is not sorted as it is a text field. is there any way we can make it a date field, that sorts my months Sep-19, oct-19 , nov-19 and so on automatically.

               

               

              also as of now I am having only 2 months data , in future I will be getting future months data ( nov , dec , jan …. so on), Do I need to pivot the future months each time a month get append in the excel?

              • 4. Re: Pivot a Data from Excel
                Hari Ankem

                We can convert it into a Date format so that the sorting will work. You can then display it in any suitable format you like.

                 

                Year-Month: DATE(DATEPARSE("YY-MMM",SPLIT([Pivot Field Names]," ",1)))

                1.png

                 

                If your data is going to continue to be in this format, then yes, you will need to pivot every month. I would rather suggest that you get the format changed so that the new monthly data come in as rows rather than columns.

                 

                Hope this helps.