6 Replies Latest reply on Jul 12, 2016 1:49 PM by Kevin Jackson

    Prepping data with month and year for use in Tableau

    Kevin Jackson

      I have a spreadsheet with several worksheets of data in the following format: ROWS = YEARS and MONTHS = COLUMNS

      Screen Shot 2016-07-07 at 1.16.34 PM.png

       

      I want to use this data in Tableau to do time-series analysis, but I'm having a difficult time. I can connect the data and pivot it, but Tableau does not see the year and month fields as date fields and therefore I can't create the time series charts I need. I got around this temporarily with one set of data by combining the month and year fields in Excel, converting them to date fields, and copying & pasting the corresponding data horizontally as one long data series. This worked, of course, but it was very time consuming and impractical for all the data I need to work with.

      Screen Shot 2016-07-07 at 1.18.29 PM.png

       

      I have researched this and apparently setting up a month-year field as a date field seems to be a common problem for Tableau. Never the less, I'm looking for the best and fastest solution by either prepping the data in Excel AND/OR modifying it within Tableau. There has to be a more elegant solution, right?

       

      This is my first post in the forum and I'm relatively new to Tableau, so can anyone point me in the right direction? Thanks!

        • 1. Re: Prepping data with month and year for use in Tableau
          Aiswarya Sundaram

          Hi Kevin,

           

          Method 1:

          Pivot in Tableau.Then using month and year columns create a calculated field for the date using DATEPARSE function.

          Like DATEPARSE('dd-MMM-yyyy','01'+'-'+[Month Field]+'-'+str([Year Field]))

          Once its a date field, you have all those MY and other options readily available.

           

          Method 2:

          You could also use the Tableau reshape plug-in for Excel.

          I'm not sure if the community still uses it but I do in my company as there are times when I like to prep the data in Excel and then bring it into Tableau.

           

          It would result in the same pivot that you would achieve in Tableau.

          Year      Month      Value

          2016      Feb      20909

          2016      Mar      29018

          Now you can go ahead and create a date field in Excel itself or take into Tableau again

           

          These are my suggestions and hope it helps.

          Welcome to the community!

          1 of 1 people found this helpful
          • 2. Re: Prepping data with month and year for use in Tableau
            Kevin Jackson

            I used the first method to PARSE the date and it worked perfectly. I knew there had to be a way and a relatively easy one at that. Thanks so much. What a great introduction to this community. Best!

            • 3. Re: Prepping data with month and year for use in Tableau
              Shawn Wallwork

              Welcome to the Forums Kevin! (Psst, don't forget to mark Aiswarya's answer correct.)

               

              --Shawn

              • 5. Re: Prepping data with month and year for use in Tableau
                Shawn Wallwork

                Yeah. I just went ahead and did it for him. No harm, no foul. We all took time to learn the ropes.

                 

                Cheers,

                 

                --Shawn

                • 6. Re: Prepping data with month and year for use in Tableau
                  Kevin Jackson

                  Ok, folks, I'm looking to take my workbook to the next level. I have imported visitor data for Utah's Big 5 national parks. I learned the hard way I had to import each of the parks data individually in order to pivot the data without it all being aggregated. I then had to blend the data within Tableau. All simple enough and I created three charts published here:

                   

                  See published Tableau workbook here

                  https://public.tableau.com/profile/kevin.w.jackson#!/vizhome/UtahBig5AttendanceTrends/Big5CampaignResults

                  Tab 1: Simple attendance charts to illustrate relative attendance sizes of the 5 parks

                  Tab 2: Aggregated attendance trend over the past 30 years with a median attendance and trend lines

                  Tab 3: Aggregated attendance trend over the past 10 years with a line denoting BEFORE and AFTER the beginning of Utah's "Big 5" promotion with television commercials causing attendance to increase significantly. (The trend line really makes this case nicely!)

                   

                  NEXT STEPS

                   

                  Here is what I'd like to do and I'm not sure how to do it, so if anyone can give me some direction I'll work on figuring it out.

                   

                  I want to compare what the EXPECTED organic attendance growth would have been since March 2013 (based on the previous 7 years attendance) compared to the ACTUAL attendance growth since March 2013. The difference between these two variables, of course, can be attributed to the "Big 5" marketing campaign.

                   

                  I have already charted ACTUAL attendance growth, so the real question is how to graph EXPECTED organic growth from March 2013 to present (based on the attendance data from January 2007-March 2013)? I know what I want to do, but I have no idea how to do this? I believe this would require a calculated field, but I'm not advanced enough to know how to do more than simple calculated expressions.

                   

                  If I'm not clear or explaining myself correctly, please reply with any questions. Thanks in advance, your humble Tableau analyst in training!