Gantt Chart Filter to Limit Data to only Records active Between Dates

user028768

I have a Gantt card that uses a Start Date and and End Date to show the span of each record.  Some records can span multiple months, quarters.  I want my end users to leverage the filter in the upper right hand corner so that they can more clearly see the records that should be in process during a date range that they choose.  In short, I want to keep the records on the card if the span of that record overlaps for at least 1 day with the user selected date range -- if not, then the record drops from the card.

 

For example: A user wants to find records active between 10/1/2020 and 12/31/2020.  I'd want them to enter their custom dates as a "between" filter and then see:

1) Records that start before the user's date range but end during the user's date range.  Ex: starts 9/1/20 and ends 11/1/20, 

2) AND records that start before the user's date range but end after the user's date range  Ex: starts 9/1/20 and ends 3/1/21,

3) AND records that start and end during the user's date range (ex: starts 11/1/20 and ends 11/30/20),

4) AND finally records that start during the user's date range and end after the user's date range (ex: starts 11/1/20 and ends 3/1/21).

 

If a record ends before the user's date range begins - then it should be excluded.

If a record starts after the user's date range end - then it should be excluded as well.

 

Any suggestions on how to conquer this one?

jaeW_at_Onyx

To boil it all down.

 

A filter can only apply on the values in the column.

I.e. you can create a filter that says I want all values that are BETWEEN or LESS THAN or GREATER than start date or end date.

You could ask your users to apply filters on two columns.  Start Date is Less than X and End Date is Greater Than Y.

 

I don't think that's  the experience you want.

To create 'isActive' you could generate one row per month or one row per day for all the projects that were active on that date, and then users can filter on "Give me all projects that were active on that date." 

SELECT 

p.*

FROM

Project p

JOIN Date d

on p.startDate <= d.date

AND p.endDate >= d.Date

just figure out if how you want to handle projects that started or ended during a month.

 

You would have to build a custom app to report "all projects that were active between a range of dates"  OR build a webform that captures user defined start date and end date, then get creative with a Fusion + Date Dimension that can live filter the dates for you.