6 Replies Latest reply on Mar 31, 2016 10:03 AM by Michael Hesser

    Alerting the holiday between 2 date range with color coding

    Raghu Anisur

      Hi Gurus,

       

      I have a date range, Start date and end date and I would like to alert the holiday between the date range for my events with the alert(like color coding that  holiday date) .we want to make this alert before 4 weeks of the holiday to know and plan the event.

       

      Is there any way we can do that in tableau.

       

      Any ideas on that would be greatly helpful.

       

      Thanks,\

      Anisur

        • 1. Re: Alerting the holiday between 2 date range with color coding
          Kenneth McBride

          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?

           

          Ken

          • 2. Re: Alerting the holiday between 2 date range with color coding
            Raghu Anisur

            Hi Ken,

             

            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.

            • 3. Re: Alerting the holiday between 2 date range with color coding
              Kenneth McBride

              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.

               

              Ken

              • 4. Re: Alerting the holiday between 2 date range with color coding
                Raghu Anisur

                Hi Ken,

                 

                I have even the list of holidays with the dates .Please refer this attached package.

                 

                Not sure how to achieve this functionality in tableau.

                 

                Regards,A

                Anisur

                • 5. Re: Alerting the holiday between 2 date range with color coding
                  Raghu Anisur

                  Hi Ken,

                   

                  Any inputs on this, if  you know about this already.

                   

                  Greatly helpful.

                   

                  Regards,

                  Anisur

                  • 6. Re: Alerting the holiday between 2 date range with color coding
                    Michael Hesser

                    Hi Anisur;

                    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.