2 Replies Latest reply on Jun 5, 2018 4:49 PM by Tim Zalk

    Complex Pivot in Prep

    Tim Zalk
    Tim Zalk Jun 5, 2018 2:41 PM

      My organization uses a spreadsheet template in the following format for sales quotas (can't share any real data, flow file, or workbooks):

      SKUCustomerJanuary - UnitsJanuary - Gross SalesFebruary - UnitsFebruary - Gross SalesMarch - UnitsMarch - Gross Sales
      10001Customer A165$2,917152$2,455197$3,339
      10001Customer B243$4,055210$3,690307$5,404
      10002Customer B57$1,56243$1,11072$1,933
      10003Customer A127$2,902105$2,182149$3,144
      10003Customer C101$2,16788$1,951116$2,415

       

      To pull it into Tableau, I'm trying to get it to look like this:

      SKUCustomerDate
      		UnitsGross Sales
      1001Customer A2018-01-01165$2,917
      1001Customer A2018-02-01152$2,455
      1001Customer A2018-03-01197$3,339

       

      Any ideas on how to achieve this in Tableau Prep?

      I can't for the life of me figure out how to get this done. The closest I can get is getting the units and gross sales into two different records (with fields quota type, quota value), but this makes building viz's overly complex.

        • 1. Re: Complex Pivot in Prep
          Miguel Escobar
          Miguel Escobar Jun 5, 2018 3:52 PM (in response to Tim Zalk)

          You could unpivot the dataset, but then there's no way to pivot it again using the "gross sales" and "units".

          Here's an article which explains a way to accomplish what you want. It's still quite complicated and not that flexible, but it gets the job done.

           

          For the part on transforming Text based month names into dates, you could create a really well done formula (wish that I could help with that!), or you could have another table that would act as a translation table with values like:

          January -- 2018-01-01

          February --- 2018-02-02

          March --- 2018-03-01

           

          and do a simple merge/union operation for that. Like it was some sort of VLOOKUP

          • 2. Re: Complex Pivot in Prep
            Tim Zalk
            Tim Zalk Jun 5, 2018 4:49 PM (in response to Miguel Escobar)

            That's it! Once I split the original sheet into two concurrent pivots, it was easy! Thanks for the article!