4 Replies Latest reply on Apr 11, 2012 2:52 AM by ivarlangedijk

    2 time-series in 1 graph

      Dear Experts,

       

      I am struggling to plot 2 time series in one graph. Each time series has different date/time stamps. When trying to plot this in Tableau, the first date/time dimensions of time series 1 become leading. This means that the values of time series 2 are plotted according date/time of time series 1. In Excel this graph can be created with a scatter plot. How do I do this in Tableau?

       

      Attached are two files (Excel file with data and scatter plot & Tableau TBW file with an attempt to create a similar plot) that I created as a simplified example. In reality I am trying to do the same on a larger data set queried from different databases.

       

      Any suggestions are welcome!

       

      Thanks.

       

      Regards,

      Ivar

        • 1. Re: 2 time-series in 1 graph
          Tracy Rodgers

          Hi Ivar,

           

          So the view shown in your Tableau example is what you would like or no?

           

          -Tracy

          • 2. Re: 2 time-series in 1 graph

            Hi Tracy,

             

            I will try to clarify my question. We would like to re-create the scatter plot shown in the Excel file in Tableau. But because the date/time dimensions of both time series are not the same, Tableau automatically makes the date/time dimension of time series 1 leading. This means that the values of time series 2 are plotted according the date/time of time series 1 (instead of 2).

             

            Regards Ivar

            • 3. Re: 2 time-series in 1 graph
              Jonathan Drummey

              Hello Ivar,

               

              The plot you drew in Excel was made up of two independent series. However, when you brought the data into Tableau, you brought it in as a single table, so Tableau treated both data sets as part of one table. That's why Tableau would draw the dates using the series 1 timestamp. You can see this even more if you add time series 2 to the graph, I set this up as a dual axis in the attached workbook and you can see the F2 and F4 series drawn with only two marks for time series 2, since there are only two entries for time series 2.

               

              In order for Tableau to treat the two series as independent data sets and put them on one axis, we need a union of the two datasets. I created this using the following custom SQL:

               

              SELECT [Sheet1$].[F2] AS [F data],
                [Sheet1$].[time series 1] AS [time series],
                "series 1" as [series ID]
              FROM [Sheet1$]
              WHERE [Sheet1$].[F2] <> "value 1"
              UNION
              SELECT [Sheet1$].[F4] AS [F data],
                [Sheet1$].[time series 2] AS [time series],
                "series 2" as [series ID]
              FROM [Sheet1$]
              WHERE [Sheet1$].[F4] <> "value 2"

               

              I had to add the WHERE clauses to filter out the header rows in the source data that were confusing Tableau as to the proper datatypes for the data.

               

              Once you have this reshaped data, then it's very easy to plot in Tableau, as in the 3rd worksheet.

               

              Jonathan

              • 4. Re: 2 time-series in 1 graph

                Hello Jonathan, thanks for the reply! This is exactly what we were looking for.

                 

                Regards Ivar