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.

user028768

Thanks for the response, @jaeW_at_Onyx .  This is a bit more than I was hoping would be necessary - particularly with the custom app or web form.  And, to add a bit more complexity, some of my end dates aren't really populated, and the gantt chart leverages the "Fill Unspecified End Dates" option to graph this well into the future.

 

My thought was that there might be a best mode calculated field that I could create, and then use that as my date range field on the filter.  Is there a way to establish a field in Domo as a user entered parameter field (ex: Start Date / End Date fields where user manually inputs a date), and then leverage the input values from those fields into a beast mode calculation?  I'm familiar with how to do this in Tableau, but not how to do this in Domo.

jaeW_at_Onyx

Domo doesn't support parameterized queries the way you're thinking.

 

But you can hack a solution

https://www.youtube.com/watch?v=wmMrnPO9ivY

 

 

user028768

Thanks @jaeW_at_Onyx - appreciate the video assist.  I'll give it a try.