Gantt Chart Filter to Limit Data to only Records active Between Dates
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?
Comments
-
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.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
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.
0 -
Domo doesn't support parameterized queries the way you're thinking.
But you can hack a solution
https://www.youtube.com/watch?v=wmMrnPO9ivY
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Thanks @jaeW_at_Onyx - appreciate the video assist. I'll give it a try.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive