4 Replies Latest reply on Mar 20, 2013 7:12 PM by Norma Rodriguez

    Preprocessing in Tableau

    Norma Rodriguez

      I would like to know if I can do the following preprocessing of my data using Tableau rather than Excel?

      My data source looks like this:

       

      Table #1

      Product IDSubscription Start DateSubscription End DateMonthly Revenue
      P11/1/20111/1/2013500.00
      P21/1/20111/1/2013200.00
      P28/1/20119/1/2012300.00
      P38/1/201112/1/2012150.00
      P41/1/20118/1/2012100.00
      P49/1/201212/1/2012200.00

       

      I want to create an area chart of the monthly revenue per product over time, to do so I want to:

       

      1. Break up the subscription time into each of the months to look something like this:

       

      Table #2

      MonthProduct1Product2Product3Product4
      January-11500200100
      February-11500200100
      March-11500200100
      April-11500200100
      May-11500200100
      June-11500200100
      July-11500200100
      August-11500500150100
      September-11500500150100
      October-11500500150100
      November-11500500150100
      December-11500500150100
      January-12500500150100
      February-12500500150100
      March-12500500150100
      April-12500500150100
      May-12500500150100
      June-12500500150100
      July-12500500150100
      August-12500500150100
      September-12500500150200
      October-12500200150200
      November-12500200150200
      December-12500200150200
      January-13500200

       

      Can I use a formula that automatically creates a field for each of the months included in the subscription period as shown in table #2?

      Can I turn my table #1 into a table #2 within Tableau or is it necessary to use the add-in for reshaping data in excel?

       

      I really appreciate your help,

      Norma

        • 1. Re: Preprocessing in Tableau
          Andrew Ball

          Hi Norma,

           

          Tableau won't allow you to create rows the way you are looking for. There is a technique using data padding and a scaffold data source, but even that will need you to do some work in Excel.

          The best way, ideally, would be to reshape the data in Excel, with the end result looking like the following:

           

          MonthProductAmount
          Jan-11P1

          500

          Jan-11P2200

           

          Note that this is only a breakdown of month, not exact days (some of your subscriptions started and ended on days during the month).

           

          If this isn't possible, however, what do you think you are able to get to easily? Is the reshape possible, or do you need a different solution?

           

          Andrew

          1 of 1 people found this helpful
          • 2. Re: Preprocessing in Tableau
            Norma Rodriguez

            Hi Andrew

            Thank you very much for your quick answer.

            Reshaping my data the way you are suggesting works perfectly, Tableau aggregates the revenue and creates a very nice chart!

            However we want to do the pre-processing step automatically, so I was considering using a database to store the data I have in the excel file, then use a store procedure and SQL clauses to reshape the data and store it in another table the way I need it and make the process automatic.

            Andrew is there any other more efficient, less time consuming solution that you could suggest me?.

             

            Also where I can get more information about padding technique and scaffold data source?

             

            Thank you very much.

             

            Norma

            • 3. Re: Preprocessing in Tableau
              Jonathan Drummey

              Hi Norma,

               

              Do a search on padding or scaffolding on the forums and you'll see a number of different posts.

               

              In your case, all you need is a table with a column of months and a cross product query that looks something like this pseudo-sql:

               

              SELECT Months.Month,

              table1.Product ID,

              table1.Subscription Start Date,

              table1.Subscription End Date,

              table1.Monthly Revenue

              FROM Months, table 1

              WHERE table1.Subscription Start Date >= Months.Month AND table1.Subscription End Date <= Months.Month

               

              You could do this in Custom SQL in Tableau on the Excel file (just add another worksheet with the Months).

               

              That will give you a row for every product and month is has Revenue in.

               

              Then you'd create the table 2 in Tableau using Month on Rows and Product ID on Columns, with SUM(Monthly Revenue) on the Text Shelf.

               

              Jonathan

              • 4. Re: Preprocessing in Tableau
                Norma Rodriguez

                Thanks Jonathan

                That works just perfect!