Can you duplicate your datasource, and blend both datasources. (Link with Defect ID).
Now keeping your original chart same, Right click on the "Close Date" from the second Datasource and drag it on top of the old field. -> Select Continuous.
Do Dual Axis and Synchronize axis, and it should give what you want.
Hope I understood your problem right. Let us know if this helps.
Karthik's solution will work as long as you have matching data for every Open and Close date. However, in Clay's example, it wouldn't catch that Defect ID 2 had closed, because there is no matching Open Date for February 15th.
Tableau will handle this type of data best if you have a slightly different data structure. This will work better:
Defect ID Status Date 1 Close Date 1/31/17 1 Open Date 1/1/17 2 Close Date 2/15/17 2 Open Date 1/11/17 3 Close Date 3/2/17 3 Open Date 1/21/17
You can augment your data to work this way by using the "Pivot" function in the Data Prep window of Tableau. It won't change the underlying data, just how Tableau sees it.
Then just create a viz that uses Date as a continuous axis on columns and count of Defect ID on rows. You can use Status on color to get two different lines.
The problem is that when I apply a filter (for example, I only want to look at a certain date range), the filter will either include the defect and both its open and close dates, or exclude the defect and both its open and close dates. I don't want this. I want to display the a defect on the open trend if it's been opened in the data range specified, even if it hasn't been closed in that same date range. Similarly, I'd want to display a defect on the closed trend if it has been closed in the date range specified, even if it was opened years and years ago. But I still only want to view the date range specified. If I want to see 12 months of data, I want to view 12 months of opened and closed defects independently, yet on the same visualization. Does this make sense?
If i'm understanding your suggestion correctly, because you're suggesting linking the two data sources, the issue is still present. Both dates will either be included or excluded, while I need the ability to show each of the dates independently.
Charles, this is the type of solution I was expecting. I'm trying to use the pivot function you have suggested, but I don't seem to have the pivot option. I may be selecting the wrong rows. Any help?
If you go into the data source window (click on the data source tab in the bottom left corner of your screen) you should be able to select the columns you want to pivot and then right-click to pivot them. Screenshot below.
This capability is available in Tableau v9.0 and later. I downloaded your workbook and tried it - it worked.
Or, right from your viz, you can select the two date fields and choose Transform > Pivot:
Hope that helps!
Interestingly, in my real workbook with real data, it will not allow me to pivot. I'm accessing a Microsoft SQL Server. I'll have to troubleshoot further.
Ah. Now I understand. It doesn't work for SQL Server - Pivot is used for files. Sorry for the confusion.
Alternative solution - you could use a master date table - a table with a record for every date in the range you want to analyze. You can blend/join this table with the data you have to achieve the same pivot.
A lot of databases already have a master date table built. Or you could create one in a file format and blend it in.