Show all dates between start and end date columns on calendar card
I have three columns; 'user name', 'start date' and 'end date' representing time off from work. I'm wanting to use a calendar card and show the days where someone will be off work. Currently, I can only use one date column which I've chosen 'start date', however, it only represents the first day someone is off work.
Is there a way to show all dates between the 'start date' and 'end date' that can be used on the calendar card to show each day someone is off work based on the 'start date' and 'end date'?
Comments
-
You can accomplish this by using a SQL transform to combine your "time off" data with the Domo Calendar dataset (Domo Dimensions > calendar.csv). The code below should give you a good starting point to edit to fit your needs.
----------
SELECT
Off.*,
Cal.dt
FROM tbl_TimeOff AS Off
INNER JOIN tbl_DomoCalendar AS Cal
ON Cal.dt >= Off.`start date`
AND Cal.dt <= Off.`end date`
----------
This essentially "creates" an individual record for all dates between your start and end dates. Should give you the data you're looking for. Hope this helps!
----------
Serving up data insights since 2002...5 -
Still working on this solution. I didn't have any luck with the above example, but I'll post back once we solve it.
0 -
How are you not having any luck?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
the MYSQL solution will work just fine provided you have a date dimension (just make sure your start date and end date are actually DATE columns not DateTime AND make sure the start and end date have default values if they were left as NULL.
Alternatively you can try to see if the Gantt chart will work for your requirements, but I prefer the first implementatation as it allows more flexibility in visualization.
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
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive