4 Replies Latest reply on Jan 22, 2018 11:00 AM by Matthew Hansen

    Gantt Chart - Lose of Bar when Filter date is between start and end date

    Jess Nauman

      Hello,

       

      A gantt chart is created using a start date and duration in days as size.  I want to be able to filter on start date which is continuous.  The problem I am having is that when the filter is set between the start date and the end date (as determined by the duration) of an activity, the bar for the activity disappears, see example below.  I want the bar to remain since it is still on-going.  I want the bar to only disappear if the filter is later than the finish date.  I have seen examples of gantt's in tableau where the bar does not go away.  What am I doing wrong?

       

      The sheet shots below gives an example.

      I want to filter by DS Start date to Feb 1, 17.  But I do not want the Bothell - Resupply line to disappear.

      Here I did the filtering and the bar was lost.

      See the bar lost, but I still want to see it since the activity is still on-going.

        • 1. Re: Gantt Chart - Lose of Bar when Filter date is between start and end date
          David Li

          Hi Jess! Can you post a packaged workbook (with data sources extracted) for us to see?

           

          Without that, my best guess would be that you're creating a Gantt chart by putting the start date on the rows shelf and putting the duration on the size mark. If that's the case, when you filter out the start date, that bar will indeed go away. As far as I know, the only way around this is to change your data source so it has a record for each day, not just one record for all contiguous days.

          • 2. Re: Gantt Chart - Lose of Bar when Filter date is between start and end date
            Jess Nauman

            Thank you David.  OK.  From your reply, it sounds like I am not missing anything. It will not work using a Duration on the size mark added to the DS start.

             

            I will work in the month timeframe.  Is there an easy way to "record for each month"?  Do I have to create a time point for each month?

            • 3. Re: Gantt Chart - Lose of Bar when Filter date is between start and end date
              David Li

              You're welcome!

               

              Yes, to create a record for each month, you'll have to edit your data source so that each month has its own row or record for each Purpose (or whatever your granularity level is in the data source).

              • 4. Re: Gantt Chart - Lose of Bar when Filter date is between start and end date
                Matthew Hansen

                We have a Gantt chart with extended dates quite far into the future but sometimes want to focus on near-term dates (i.e. upcoming deadlines) to better see details. Using filters would exclude projects we're working in parallel. Fixing the axis is not sustainable.

                 

                To get around these issues I created a set of calculated fields named "Start Date Visible Axis", "End Date Visible Axis", and "Duration Visible Axis". Initially I had these fields fixed at a specific start (subtract number of days from current date) and duration length but switched to parameters.

                 

                I created two parameters named "Days Prior to Today Parameter" and "Duration Days Parameter". Default in my case was set to 60 for both to represent a condensed timeline centered on the current date.

                 

                Note: In my original post the calculated fields were simple but garbage data forced some complexity.

                 

                Calculated field for "Start Date Visible Axis":

                //Missing start date but end date falls within specified date range then show end date

                IF ISNULL([Start date]) = TRUE AND [Target Delivery Date] < TODAY() + [Duration Days Parameter] AND [Target Delivery Date] > TODAY() - [Days Prior to Today Parameter] THEN [Target Delivery Date]

                //Missing start date but end date is greater than specified date range then null

                ELSEIF ISNULL([Start date]) = TRUE AND [Target Delivery Date] > TODAY() + [Duration Days Parameter] THEN NULL

                //Missing start date but end date is less than specified date range then null

                ELSEIF ISNULL([Start date]) = TRUE AND [Target Delivery Date] < TODAY() - [Duration Days Parameter] THEN NULL

                //Start date is greater than specified date range then null

                ELSEIF [Start date] > TODAY() + [Duration Days Parameter] THEN NULL

                //End date is less than specified date range then null

                ELSEIF [Target Delivery Date] < TODAY() - [Days Prior to Today Parameter] THEN NULL

                //Start date is less than specified date range then lower range of visible parameter

                ELSEIF [Start date] < TODAY() - [Days Prior to Today Parameter] THEN TODAY() - [Days Prior to Today Parameter]

                //Final date

                ELSE [Start date] END

                 

                Calculated field for "End Date Visible Axis":

                //Missing end date but end date falls within specified date range then show start date

                IF ISNULL([Target Delivery Date]) = TRUE AND [Start date] < TODAY() + [Duration Days Parameter] AND [Start date] > TODAY() - [Days Prior to Today Parameter] THEN [Start date]

                //Missing end date but start date is greater than specified date range then null

                ELSEIF ISNULL([Target Delivery Date]) = TRUE AND [Start date] > TODAY() + [Duration Days Parameter] THEN NULL

                //Missing end date but start date is less than specified date range then null

                ELSEIF ISNULL([Target Delivery Date]) = TRUE AND [Start date] < TODAY() - [Duration Days Parameter] THEN NULL

                //Start date is greater than specified date range then null

                ELSEIF [Start date] > TODAY() + [Duration Days Parameter] THEN NULL

                //End date is less than specified date range then null

                ELSEIF [Target Delivery Date] < TODAY() - [Days Prior to Today Parameter] THEN NULL

                //Target date is greater than specified date range then higher range of visible parameter

                ELSEIF [Target Delivery Date] > TODAY() + [Duration Days Parameter] THEN TODAY() + [Duration Days Parameter]

                //Final date

                ELSE [Target Delivery Date] END

                 

                Calculated field for "Duration Visible Axis":

                     DATEDIFF('day',[Start Date Visible Axis], [End Date Visible Axis])

                 

                Then I substituted these values into the Gantt chart. However, I did put the original Start Date and Duration into the Tooltip. I exposed the parameters providing a convenient method to dynamically shift the Gantt chart between different start/end dates without excluding records outside this date range. Also added a reference line for current date.

                 

                Gantt Chart Dynamic.png