3 Replies Latest reply on Nov 23, 2018 4:51 AM by Ankit Bansal

    (10.2) How to pivot multiple fields

    David Holderness

      Hi All

       

      I've been provided with some data that needs some work doing to it before it'll be good to work with.

       

      I cannot share the source but i've mocked it up below:

       

      Capture.PNG

       

      The data currently has this structure. It would be unwieldly to work with in tableau, is there a way to automate the process to achieve something like this:

       

      Capture.PNG

       

      There is something like 800 people, over several quarters and 20 KPIs so doing this manually just isn't an option.

       

      Any ideas much appreciated

        • 1. Re: (10.2) How to pivot multiple fields
          Ankit Bansal

          David,

           

          In the data source pane you can simply select all these 4 fields except name, right click and click pivot. With this you will get 4 rows for each row in your source like:

           

          David, KPI 1 Num,40

          David, KPI 1 Denom,80

          David, KPI 2 Num,50

          David, KPI 2 Denom,60

           

          It is not exactly what you are looking for but i think this should also work for you.

           

          Other option is to 2 union the same dataset.

           

          Thanks,

          AB

          • 2. Re: (10.2) How to pivot multiple fields
            David Holderness

            Hi Ankit

             

            Thanks for your reply.

             

            It's important that the data has the KPI Name - Numerator - denominator structure to it so it will function well with other work i am doing.

             

            Having each numerator and denominator be its own line would also create work for creating percentages

             

            could you explain your union thinking?

             

            Many Thanks

            • 3. Re: (10.2) How to pivot multiple fields
              Ankit Bansal

              Ok. In union you can do union the same dataset with itself.

              So let say your first instance of filed is called file1 and second file2. A new field table name will be created with there 2 names.

               

              then create a calculated field that KPI that says:

               

              If table_name='file1' then 1 else 2 end'

               

              numerator will be :

               

              If table_name='file1' then [KPI 1 numerator] else [KPI 2 numerator] end

               

              similarly

              denom will be

               

              If table_name='file1' then [KPI 1 denom] else [KPI 2 denom] end

               

              Now use these 3 fields + name for your calculation.

               

              Thanks,

              AB