7 Replies Latest reply on Jun 19, 2017 11:04 AM by Charles Schaefer

    How to synchronize 2 date fields and filter independently?

    Clay McNeff

      I have a list of defects. Each defect has an open date and a close date. See below for example data.

        

      Defect IDOpen DateClose Date
      11/1/20171/31/2017
      21/11/20172/15/2017
      31/21/20173/2/2017
      41/31/20173/17/2017
      52/10/20173/31/2017
      62/20/20174/16/2017
      73/2/20174/30/2017
      83/12/20175/16/2017
      93/22/2017
      104/1/2017

       

       

      I'd like to overlay the number of defects opened per month and the defects closed per month on one visualization (both line graphs). For example, if I only wanted to see February data, I'd like the visualization to display 2 defects opened in Feb and 1 defect closed in Feb. But if i use either of the date fields to filter the data, it will include/exclude at the row level. A filter saying I only want defects opened in February will only display defects 5 and 6, even though I'd still like to see that defect 2 was closed this month. If I filter on 'close date', then i lose any defects that have been opened but not since closed.

       

      Is there a way to essentially split this single data source or create a third date field so that I can show the number of opened defects per month and the number of closed defects per month in one visualization without filtering out wanted data or including unwanted data?

       

      Thanks

        • 1. Re: How to synchronize 2 date fields and filter independently?
          Karthik Venkatachalam

          Hi Clay,

          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.

          Thanks!

          • 2. Re: How to synchronize 2 date fields and filter independently?
            Charles Schaefer

            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 IDStatusDate
            1Close Date1/31/17
            1Open Date1/1/17
            2Close Date2/15/17
            2Open Date1/11/17
            3Close Date3/2/17
            3Open Date1/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.

            • 3. Re: How to synchronize 2 date fields and filter independently?
              Clay McNeff

              Karthik,

               

              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.

              • 4. Re: How to synchronize 2 date fields and filter independently?
                Clay McNeff

                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?

                • 5. Re: How to synchronize 2 date fields and filter independently?
                  Charles Schaefer

                  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.

                   

                  Screen Shot 2017-06-19 at 12.23.49 PM.png

                  Or, right from your viz, you can select the two date fields and choose Transform > Pivot:

                   

                  Screen Shot 2017-06-19 at 12.32.10 PM.png

                   

                  Hope that helps!

                  • 6. Re: How to synchronize 2 date fields and filter independently?
                    Clay McNeff

                    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.

                    • 7. Re: How to synchronize 2 date fields and filter independently?
                      Charles Schaefer

                      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.