5 Replies Latest reply on Nov 6, 2019 5:10 PM by swaroop.gantela

    How to limit the view of a Gantt chart to certain dates

    Matt B

      Hi Everybody,

       

      I built a Gantt chart and I currently filter the results by start date, but I want to be able to see all activities for each project within defined window of time. For instance, if I set the value of the time filter to the past 2 years, I’d like to see not only the phases that started in the past 2 years, but also the phases that continued during these past 2 years no matter if they come to an end during this time or not.

       

      Similarly, I need to see the phases that are still ongoing in this window but I am not interested to see the end point if they are set to finish beyond the current date. Briefly speaking, I want to create a snapshot for the selected time frame something like the window between the 2 reference line in the below image. I’ve attached a Tableau workbook for your reference.

       

      Any idea is well appreciated.

       

      Thanks,

       

      Matt

       

        • 1. Re: How to limit the view of a Gantt chart to certain dates
          swaroop.gantela

          Matt,

           

          I made an attempt, I think it may be doable with a few calculated fields:

           

          1. Beginning of Window:
              DATEADD('year',-2,TODAY())

           

          2. Plotted Start Date in Window:

           

          IF [Start Date]<=[Beginning of Window]          //begins before the window beginning
          AND [End Date]>=[Beginning of Window]      //ends after the window beginning
          THEN [Beginning of Window]                        // then use the window beginning, not true start date

          ELSEIF [Start Date]>=[Beginning of Window]      //begins after the window beginning
          AND [Start Date]<=TODAY()                                //and before today
          THEN [Start Date]                                                //just return the start date
          END

           

          3. Plotted End Date

          IF [End Date]<=[Today]  // don't allow hang over Today

          THEN [End Date]

          ELSE [Today]

          END

           

          4. Plotted duration

          DATEDIFF('day',[Plotted Started Date in Window],[Plotted End Date])

           

          Please see workbook 2019.3 attached in the Forum Thread:

          How to limit the view of a Gantt chart to certain dates

          321957window.png

          • 2. Re: How to limit the view of a Gantt chart to certain dates
            Matt B

            This looks great, thanks Swaroop!

             

            Just a quick note: I used the 2 year window as an example. I want to be able to filter results without any limitation. Preferably using the relative date filter option so the user can see results for previous year, past 3 years, or even past month, etc.

            • 3. Re: How to limit the view of a Gantt chart to certain dates
              swaroop.gantela

              Matt,

               

              Hmm. That sounds tricky.

              There generally isn't a way to know what a user has selected in a filter,

              particularly one as free entry as that.

               

              A whole other approach would be to set up a date scaffold that contains

              every date you would be interested in.

              Then your data set would be joined to the scaffold, and it would keep

              every scaffold date between the start and the end.

              Then your free form date filter would be created from the scaffold date and it

              would retain just the dates in the window.

               

              The drawback is that it might considerably enlarge your dataset.

              How many rows are in your true data set?

               

              If you think the resulting joined dataset would be manageable in size,

              we can explore that option.

               

              Here are some links on the subject of date scaffolds:

              CROSS JOIN with Tableau's join dialog

              https://www.kenflerlage.com/2019/03/date-scaffold.html

              • 4. Re: How to limit the view of a Gantt chart to certain dates
                Matt B

                Hi Swaroop,

                 

                My actual data is coming from SQL Server. I used union and put both start and end dates in one column then set up a scaffold date. In addition I followed the instruction on here for an easy fix for the problem- no result! I also used parameters to dynamically change the length of the window in your solution, yet the end dates extend way beyond the window ends up until the process is completed. Can't believe something this simple becomes such an overwhelming headache. Any suggestion?

                 

                Thanks,

                 

                Matt

                • 5. Re: How to limit the view of a Gantt chart to certain dates
                  swaroop.gantela

                  Matt,

                   

                  I mocked up something here.

                  I think the start and end dates need to stay in different columns

                  and then are joined to the scaffold as shown below.

                   

                  The relative filter you described is applied to the scaffold date.

                  I also added a filter for dates less than Today.

                  Then I made some LODs to get the min and max dates in the selected window

                  and the created the Gantt as before.

                   

                  One issue is that projects that are not in the window don't show up,

                  in your first version they were there but had a blank row. Is that ok?

                   

                  Please see workbook and datasource attached in the Forum Thread.

                   

                  321957windowB.png