5 Replies Latest reply on Jan 18, 2018 2:22 PM by Shinichiro Murakami

    How can I get successive dates based on number of weeks ?

    Jacob Turner

      I have a need to show successive dates in a viz without padding the data. I have a start date and end date and I need to show the data by week, showing each product/retailer combo in each week that it is on ad.

       

      For example, my crosstab currently looks like this:

       

      Start Date    Account      Product       Weeks on Ad

      1/2/2017      Retailer 1    Product 1        1

      1/2/2017      Retailer 2    Product 1        4

      1/9/2017      Retailer 3    Product 3        1

      1/16/2017    Retailer 4    Product 4        1

       

      But I need it to look like this:

       

      Start Date    Account      Product       Weeks on Ad

      1/2/2017      Retailer 1    Product 1        1

      1/2/2017      Retailer 2    Product 1        4

      1/9/2017      Retailer 3    Product 3        1

      1/9/2017      Retailer 2    Product 1        4

      1/16/2017    Retailer 4    Product 4        1

      1/16/2017    Retailer 2    Product 1        4

      1/23/2017    Retailer 2    Product 1        4

       

      Not necessarily in that order, but if an item is on ad for more than one week, I need it to show up for each successive week that it is on ad. Does anyone know how I can do this in Tableau? I've attached a crude mockup of my current view.

       

      Thanks!

        • 1. Re: How can I get successive dates based on number of weeks ?
          Shinichiro Murakami

          Hi Jacob

           

          You still need to re-shape data.

           

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: How can I get successive dates based on number of weeks ?
            Jacob Turner

            Thanks, Shin. That will actually be very useful for a different project I'm working on, but I'm afraid I am using SQL for this project and Tableau won't let me pivot the data. Sorry, I should have mentioned that previously.

             

            Any other thoughts?

            • 3. Re: How can I get successive dates based on number of weeks ?
              Shinichiro Murakami

              Hi Jacob,

               

              This is essential step and I don't have any otehr idea.

               

              Chasing custom SQL to pivot data, but Tableau official lost past link in online help.

               

              couple of past forum posts.

               

              Pivot Data with Custom SQL

              help with custom sql to pivot data

               

              Thanks,

              Shin

              • 4. Re: How can I get successive dates based on number of weeks ?
                Jacob Turner

                Thanks, Shin. I'll spend some time messing around with this and see what I can do.

                • 5. Re: How can I get successive dates based on number of weeks ?
                  Shinichiro Murakami

                  Link recovered.

                   

                  Shin

                   

                  Pivot Data from Columns to Rows

                   

                  Pivot using custom SQL

                  You can also use custom SQL to pivot your data. When you use the UNION ALL operator in a custom SQL query, you can take values from distinct columns and put them into a new column.

                  For example, suppose you have a table called Contest.

                  Contest

                  RunnerStart TimeEnd Time
                  Amanda9/3/2016 3:04 PM9/3/2016 3:25 PM
                  Oscar9/3/2016 3:04 PM9/3/2016 3:21 PM
                  William9/3/2016 3:04 PM9/3/2016 3:16 PM

                  To optimize your analysis of this data in Tableau, you can use the following custom SQL query to pivot the "Start Time" and "End Time" columns so that their values are in a single column.

                  Select [Runner]
                  , 'Start' as [Action]
                  , [Start Time] as [Time]
                  From [Contest]
                  Union ALL
                  Select [Runner]
                  , 'End' as [Action]
                  , [End Time] as [Time]
                  From [Contest]

                  The above custom SQL query does the following:

                  • Pivots the Start Time column header into a string value called Start and adds that value to a new column called Action.
                  • Pivots the End Time column header into a string value called End and adds that value to a new column called Action.
                  • Pivots the Start Time and End Time columns so that their values are in a new column called Time.

                  The following table shows the results of this custom SQL query.

                  RunnerActionTime
                  AmandaStart9/3/2016 3:04 PM
                  OscarStart9/3/2016 3:04 PM
                  WilliamStart9/3/2016 3:04 PM
                  AmandaEnd9/3/2016 3:25 PM
                  OscarEnd9/3/2016 3:21 PM
                  WilliamEnd9/3/2016 3:16 PM

                   

                  To pivot data using custom SQL

                  1. Connect to your data.
                  2. Double-click the New Custom SQL option in the left pane. For more information, see Connect to a Custom SQL Query.
                  3. In the Edit Custom SQL dialog box, copy and paste the following custom SQL query and replace the contents with information about your table:Select [Static Column]
                    , 'New Value (from Column Header 1)' as [New Column Header]
                    , [Pivot Column Values 1] as [New Values]
                    From [Table]
                    Union ALL
                    Select [Static Column]
                    , 'New Value (from Column Header 2' as [New Column Header]
                    , [Pivot Column Values 2] as [New Values]
                    From [Table]
                    Union ALL
                    Select [Static Column]
                    , 'New Value (from Column Header 3' as [New Column Header]
                    , [Pivot Column Values 3] as [New Values]
                    From [Table]
                    style="margin-top:9px"
                  4. New Value (from Column Header 1-3): New names that you give to the original column headers, which are used as row values in the pivot.
                  5. Pivot Column Values 1-3: The columns whose values need to be pivoted into a single column.
                  6. New Column Header: The name you give the new column that contains the new row values from New Value (from Column Header 1-3).
                  7. New Values: The name give the new column that contains the original values from Pivot Column Values 1-3.
                  8. Table: The table that you connected to.
                  9. Click OK.