7 Replies Latest reply on Feb 12, 2019 5:15 AM by Shinichiro Murakami

    Pivoting in Tableau Prep

    Thiago Gonçalves Pinto

      Hello guys,

       

      I have a dataset that is configured like this:

       

      Order NumberFirst NameLast NameTotal PaidProduct AProduct BProduct CProduct D
      10000001AaaaaAaaaaa1501111
      10000002BbbbbBbbbb1000110
      10000003CccccCcccc100001

       

      On Prep, I use Pivoting to make the quantity for Product A, B, C, D be displayed on row, and not on columns.

       

      Order number
      First NameLast NameTotal Paid
      Selected ProductsQuantity
      10000001AaaaaAaaaaa150Product A1
      10000001AaaaaAaaaa150Product B1
      10000001AaaaaAaaaa150Product C1
      10000001AaaaaAaaaa150Product D1
      10000002BbbbbBbbbb100Product A0
      10000002BbbbbBbbbb100Product B1
      ..................

       

      My problem is regarding the Total Paid. Is there a better way to manage the Total Paid filed in Tableau after using the Pivoting?

       

      On Tableau Desktop, that will be displayed by default as SUM, multiplying everything by 4 since there are 4 products on this table. The real dataset has WAY more products, so you can imagine the distortion.

       

      Is there a better way to pivot that table? Right now, I am using Min() to get the correct number (I know it could be Max or Avg).

       

      It would be nice if someone can share any best practice for the pivoting.

       

      Thanks to all in advance

        • 1. Re: Pivoting in Tableau Prep
          Shinichiro Murakami

          One other appraoch is Pivot "Total" as well.

          Total will come to the bottom of "Selected Products" which you can filter out when you only handle quantity.

           

          There might be different confusion, but just as one options.

           

          Shin

          1 of 1 people found this helpful
          • 2. Re: Pivoting in Tableau Prep
            Angel Sinha

            We faced the same issue. But in our case since the number of pivoted columns were less, we just ended up dividing the KPI with the number of pivoted columns.

             

            Very similar to what you did. 

            1 of 1 people found this helpful
            • 3. Re: Pivoting in Tableau Prep
              Joshua Milligan

              Thiago,

               

              Thiago Gonçalves Pinto wrote:

              It would be nice if someone can share any best practice for the pivoting.

              A lot depends on what kind of analysis you want to be able to do.  I don't believe that there's a single best practice that will solve every possible analysis.

               

              • I like Shinichiro Murakami's approach as it allows you to have the level of detail you want and quite a bit of flexibility.  You'll have to be careful when doing a distinct count of products as the Total Paid would also be counted if not filtered out.
              • You could keep the pivoted structure you have in your original post and then use a level of detail expression in Tableau to avoid having to remember to use MIN/MAX/AVG.  That brings some complexity related to filtering.
              • You could allocate the total cost across the product rows in Tableau Prep.  For example, you could divide the Total Cost in each detail row by the total Quantity of products and then multiply that by the quantity on each row.  In Prep, this would require an aggregation joined back into the flow.  It's probably not the "best" approach because it's rather arbitrary.  However, if you have other data (product price, for example) that would allow you to more accurately allocate the cost per product row, it might be ideal.
              • Not now, but when Tableau's data model feature is ready (it's in alpha now - you can sign up for early preview here: Tableau's New Data Model | Tableau Software) then this is a case where having two tables - one at your original level of detail (record per order with total price) and your pivoted table (record per product type per order with product quantity) - modeled correctly in Tableau, would work amazingly well!  You would just keep those structures and Tableau would understand the different levels of detail and how each measure should be aggregated correctly in the same view.

               

              Again, probably not a "best" solution, but a lot of really good options!

               

              Hope that helps!

              Joshua

              1 of 1 people found this helpful
              • 4. Re: Pivoting in Tableau Prep
                michael.lowden

                Why not just 'drop' the TOTAL column after the pivot? ... let Tableau aggregate the new rows as desired. Should be the same result with less confusion than having a repeating TOTAL or a union'd TOTAL mixed in with all the line-items. Assuming you have a table with line-item costs and any applied discounts for the transaction. Worst-Case. Fork it and aggregate it with an AVG across your primary keys inside Prep.

                1 of 1 people found this helpful
                • 5. Re: Pivoting in Tableau Prep
                  Thiago Gonçalves Pinto

                  Thank mate. Appreciated the help. I'll dive into that. Cheers

                  • 6. Re: Pivoting in Tableau Prep
                    Thiago Gonçalves Pinto

                    Thanks mate. It was very helpful. I am going to follow your suggestions.

                     

                    Cheers

                    • 7. Re: Pivoting in Tableau Prep
                      Shinichiro Murakami

                      Thank you for detail following up, Joshua!

                       

                      Shin