Hey Anisur, I'm not understanding your question. Is the Execution - End Date the Holiday you are referring to? If so, are you saying you want a visual indicator when you are less than 4 weeks of the Execution - End Date?
I have Start date and end date, I want to know the holidays between the start date and end date if any and I want to color code that holiday before 4 weeks for the particular event.
Holidays between the Start date and end date range.
I have even Release date other than Start date and End date.
Holidays.twbx 59.1 KB
It sounds like you're missing one piece of necessary information, a list of holidays and their corresponding dates. Tableau doesn't store that information so you're going to need to provide it in order to reference it in calculated fields.
Any inputs on this, if you know about this already.
It looks like this is an old post and you've likely moved on, but I wanted to share my admittedly inelegant solution that may (or may not) solve your quandary (and perhaps those of others).
I started by making a simple grid of Holidays by Program Details:
- On Columns, added MDY(Holiday Date) and Holiday
- On Rows, added Program/Event, Execution Start Date, Execution - End Date
My next task was to determine when (and which) holidays intervened with Project periods, so I wrote the following calc:
This use of DATEDIFF tells us: If a the holiday is on or after the Start of the Project AND the holiday is on or before the End of the Program, then we've got a problem.
Using the results of the Holiday Check, I was able to set both Color and Shape in the grid: this provides you with an undeniable visual representation of where holidays intervene with Project.
I did a few more things to spruce up the view:
- To provide a 4-week window, I created a Starting Week parameter with step sizes of 1 week
- I tied this to calc based on Execution - Start Date, then made a Filter out of this. This reduces the number of Projects in view to only those in the next 4 weeks
- I also tied it to a calc based on Holiday Date. This trims holidays that have passed from the view, so as Christmas approaches, you don't need to go scrolling to the other side of the screen
- I also did a few odd's n ends to help with functionality. For example, I made some calcs to show the Holiday Name and Date REAL BIG when the user hovers over them.
I'm confident someone will be able to come up with a more elegant solution: a DNA/Bar chart to show the span of the project, with Holidays dropped in??? But boy, dates in Tableau get me cranky ... if only for a nice DATEVALUE function (ala Excel) so I could plot multiple values on a single line... but I digress.
Holidays (1).twbx 50.0 KB