Show all dates between start and end date columns on calendar card

Reply
Highlighted
White Belt

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'?

Highlighted
Yellow Belt

Re: Show all dates between start and end date columns on calendar card

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...
Tags (1)
Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.