Query Based on a Value Between Two Date Columns
I've got a data set that includes job tickets with create_date and last_update columns. If the date range for a card is set to include a date in between the create_date and last_update, the ticket should be considered "open" at that point in time, and any tickets outside of the date range should be considered "closed" at that point in time. The goal is a report that shows the number of "open" tickets based on a given date range (i.e., the number of open tickets in March 2023).
Can you think of a way this could be accomplished in Domo?
Best Answers
-
You could use a variable that would allow the user to select a date. You would then create a beast mode that creates the open/closed logic. Something like this:
CASE when variablefield >= create_date and varialbefield <= last_update then 'Open'
ELSE 'Closed'
ENDYou would then put this beast mode in your filters section and either filter to open, or make it a quick filter and allow people to choose which is open or closed. Or just drag it in as another field on your card.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
you can use the date that they select with the variable and use some functions in your beast mode to apply to the entire month.
This would get you the first day of the month:
DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)
This would get you the last day of the month
LAST_DAY(`dt`)
You can use these functions while comparing to your create and last updated dates.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
You could use a variable that would allow the user to select a date. You would then create a beast mode that creates the open/closed logic. Something like this:
CASE when variablefield >= create_date and varialbefield <= last_update then 'Open'
ELSE 'Closed'
ENDYou would then put this beast mode in your filters section and either filter to open, or make it a quick filter and allow people to choose which is open or closed. Or just drag it in as another field on your card.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
@MarkSnodgrass thanks for the quick reply. Can you think of a way to use this approach to query for a date range? So not just all open tickets on a specific date, but open tickets during a date range (i.e. 3/1/2023 to 3/31/2023)
0 -
you can use the date that they select with the variable and use some functions in your beast mode to apply to the entire month.
This would get you the first day of the month:
DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)
This would get you the last day of the month
LAST_DAY(`dt`)
You can use these functions while comparing to your create and last updated dates.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
@MarkSnodgrass thanks for the suggestions. I'll give this a shot and see if I can make it work.
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
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 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