8 Replies Latest reply on Dec 30, 2016 3:39 AM by Laxman Rajagopalan

    Time series analysis

    Laxman Rajagopalan

      Hello,

       

      I am a beginner Tableau user. Need some help with time-series analysis. I can do this in excel easily, but need a few points to make this work in Tableau (10.0.3). Attached is the sales data spreadsheet with the basic pivot tables & graphs. Questions -

       

      1. I am looking to a time-series viz with Sales Vs Years (x-axis) using Category and Geographies as filters.
      2. For the same data, I am looking to do a time series with ratios (sales/total-sales).

       

      I tried many times, but could not get the viz to work like Excel. Any pointers will help.

       

      Thanks in advance.

        • 1. Re: Time series analysis
          Sarah Ebreo

          Hi Laxman,

           

          To recreate your tables and charts in Tableau first you can pivot the FY fields. To pivot the fields, in the data connection window, highlight the FY 2010 to FY 2016 fields and select Pivot. This will create two new fields, Pivot Field Names (FYs) and Pivot Field Values (Revenues). See the product guide for more info: http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#pivot.html

           

          Once the data has been pivoted, to create the Category Sales ($) graph, drag Pivot Field Names to Columns, Pivot Field Values to Rows, and place Geo onto the Color shelf.

           

          To create the Category Sales (%) graph, duplicate the worksheet then right-click Pivot Field Names on Columns and select Quick Table Calulation > Percent of Total. Then right-click Pivot Field Names again and select Compute Using > Geo.

           

          You can then right-click each worksheet and select Duplicate as Crosstab to get a table view.

           

          See the attached workbook as an example. If you have any questions about this solution, please let me know.

           

          Best,

          Sarah

          • 2. Re: Time series analysis
            Norbert Maijoor

            Hi Sarah,

             

            Did exactly the same but only "enhanced" the date date("1-1-"+str(mid([Date base],4,4))) to be able to use the full date-functionality available in Tableau.

            Find my approach as reference below and stored in attached workbook version 10.1

             

            • 3. Re: Time series analysis
              Laxman Rajagopalan

              Hi Sarah,

               

              Thanks for your response. Very helpful indeed. I have a few additional questions I am hoping that you can help me out with.

              • How can I sort the x-axis (FY) for ascending or descending?
              • What's the rationale of pivoting the data? While I did not know how to do this in Tableau (thanks for showing me how), I did consider this and was contemplating doing the same in the source data.
              • Finally, is there a way I could resize the graphs?

               

              Thanks in advance.

               

              Regards,

              -Laxman

              • 4. Re: Time series analysis
                Laxman Rajagopalan

                Hi Norbert,

                 

                Thanks much for taking time to address my problem. I am curious to know why you needed the formula that you mention and could you describe what this formula does?

                 

                Appreciate it.

                 

                Regards,

                -Laxman

                • 5. Re: Time series analysis
                  Norbert Maijoor

                  Hi Laxman,

                   

                  The time-dimension in your source-file is FY2010, FY2011-FY2016. Based on the pivoting this field gets a "technical name" "Pivot field names".

                  I renamed it to "Date base".

                   

                  Based on MID(Date base),3,4) I am taking of the FY and adding 1-1- to 20XX so it becomes a string 1-1-20xx which tableau recognizes as a date and on return you get the full date-functionality available within Tableau

                   

                  date("1-1-"+str(mid([Date base],3,4)))

                   

                  Hope it is clear.

                  • 6. Re: Time series analysis
                    Sarah Ebreo

                    Hi Laxman,

                     

                    Tableau offers some free training videos that I think will help answer your questions. I've linked some below.

                     

                    • How can I sort the x-axis (FY) for ascending or descending?
                      • To sort FY, right-click the FY field on Columns and select Sort. The following video covers several different ways to sort and there are some helpful links included in the description. Video: Sorting.
                    • What's the rationale of pivoting the data? While I did not know how to do this in Tableau (thanks for showing me how), I did consider this and was contemplating doing the same in the source data.
                      • It can be easier to work with your data if it is in a row-orientated table. The first paragraph of the Tips for Working with Your Data product guide page has a pretty good explanation on why it may be easier to work data formatted this way. Also check out the Pivot data in crosstab format section of that page. The following video may also have some good information. Video: Data Perp with Text and Excel Files.
                    • Finally, is there a way I could resize the graphs?
                      • There are a couple options for resizing the graphs. You can click and drag the sides of the graph to make them larger or smaller. You can also adjust the fit options in the tool bar to Standard, Fit Width, Fit Height, or Entire View. I recommend checking out the following links:

                     

                    Hope this helps!

                     

                    Best,
                    Sarah

                    • 7. Re: Time series analysis
                      Laxman Rajagopalan

                      Hi Norbert,

                       

                      Appreciate your response. It makes sense now :-).

                       

                      Thank you.

                       

                      Regards,

                      -Laxman

                      • 8. Re: Time series analysis
                        Laxman Rajagopalan

                        Hi Sarah,

                         

                        Thank you for the detailed answers. Very helpful indeed.

                         

                        Regards,

                        -Laxman