5 Replies Latest reply on May 18, 2018 7:04 AM by kushal acharya

    Custom Pivot

    kushal acharya

      Hello,

       

      I have attached mock workbook with "have" and "want" sheets. I wish to pivot certain part of the data according to the end users wishlist ! The format I am looking for is in the "want" sheet. Is this possible in Tableau.

       

      Please help.

       

      Thank you in advance.

       

      Kushal

        • 1. Re: Custom Pivot
          Okechukwu Ossai

          What you are actually trying to do is to unpivot the data. Tableau doesn't have a functionality called "Unpivot".

           

          However, it's very straightforward to recreate your "want" sheet. The only problem arises when you have more than 3 screenings. I can provide the solution but it won't serve you any good if your actual dataset is much bigger than this. Do you think that the number of screenings will increase in the future to say 4, 7, 10 or more screenings? If yes, then you will likely need a different solution using something like Custom SQL or VBA or an ETL tool.

           

          If the data is going to be just what you posted then I'll go ahead and send you a solution.

          • 2. Re: Custom Pivot
            kushal acharya

            Thank you for taking the time to look into my question. The number of screening are 3 and will not increase in future.

             

            Please post the solution.Thank you.

            • 3. Re: Custom Pivot
              Okechukwu Ossai

              Hi Kushal,

               

              I didn't take the data structure into consideration when I eye-balled the "want" and "have" tables initially. So, this is not straightforward as I originally thought.

               

              The problem is that screenings 1, 2 and 3 all live in different rows of the current database and bringing them all up to the same row will be a big challenge. See screenshot below. This will even become more complicated if you have more rows of data in you actual datasource than what is currently shown in the "have" table. I thought about using nested table calculations to move those red blocks up to the top. However, you will be limited in what you can do with the result. So, I rather suggest you do this outside Tableau. VBA or SQL may be the best solution for you.

               

              Ossai

               

              • 4. Re: Custom Pivot
                Okechukwu Ossai

                An alternative workaround will be to put each screening group in a separate worksheet and then combine them on a dashboard.

                 

                So, from the image in my last post, I can put each red block of data in a separate worksheet and filter out all nulls, then place all 3 worksheets side by side in a horizontal container on a dashboard. Is this kind of workaround a good fit for your needs?

                • 5. Re: Custom Pivot
                  kushal acharya

                  Thank you for looking into my question. As I have lots of data, the above workaround will not work for me.

                   

                  Thank you,

                  Kushal