So the view shown in your Tableau example is what you would like or no?
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).
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]
WHERE [Sheet1$].[F2] <> "value 1"
SELECT [Sheet1$].[F4] AS [F data],
[Sheet1$].[time series 2] AS [time series],
"series 2" as [series ID]
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.
union of time series.twbx.zip 27.3 KB
Hello Jonathan, thanks for the reply! This is exactly what we were looking for.