3 Replies Latest reply on Dec 10, 2018 8:02 AM by Don Wise

    How to Exclude Holidays and Weekends in Tableau

    karthik Reddy

      Hello All,

       

      I have a requirement  like I need to show only Working Days Data in a Tableau Report.

       

      In this I created a workbook with Order date,shp date,Number of days, excluding Weekends fields i am able to see the data correctly but when i am having Holidays list static(Example find the field "Example Holidays List") in the Attached workbook how can i exclude those days while calculating the difference between Order date and Ship date.

       

      Finally I need a View like only Working days data(Excluding Weekends and Holidays)

       

      https://public.tableau.com/views/ExcludeHolidaysandWeekends/Sheet1?:embed=y&:display_count=yes

       

       

       

       

      Thanks

        • 1. Re: How to Exclude Holidays and Weekends in Tableau
          Don Wise

          Hello Karthik,

           

          There's a massive collection of threads located here for your particular use case:  FAQ:  Holidays & Workdays 

           

          Additionally, because of what you want to do is actually count only business dates between Order Date and Ship Date and also exclude both Holidays and Weekends, there is a nicely written Knowledge Base Article here: https://kb.tableau.com/articles/howto/calculating-the-number-of-business-days-in-a-month  (actually titled Calculate Number of Weekdays between Dates).

           

          It describes how to shift your Order Date and Business Date into a Weekday (essentially excluding your weekend dates) and also how to bring in a Holiday Table and join that to your dates and then exclude those dates prior to doing an actual DateDiff calculation.  I think that KB will actually help you most.  Reason being, what if your holiday is also a weekend date and not just a weekday date?  Then you'd enter into the possibility of mis-counting the number of dates.  The date could be both a Holiday and a Weekend Date and you'd only want to count that once.

           

          Aside from that, you could create a calc like the following (call it Holidays):

           

          //      1/6/2014

          //      1/27/2014

          //      3/27/2014

          //      7/28/2014

          //      12/25/2014

          //      1/6/2015

          //      1/27/2015

          //      3/27/2015

          //      7/28/2015

          //     12/25/2015

           

           

          IF [Order Date]=#01/06/2014# OR [Ship Date]= #01/06/2014# THEN 1 //Holiday

          ELSEIF [Order Date]=#01/27/2014# OR [Ship Date]= #01/27/2014# THEN 1 //Holiday

          ELSEIF [Order Date]=#03/27/2014# OR [Ship Date]= #03/27/2014# THEN 1 //Holiday

          ELSEIF [Order Date]=#07/28/2014# OR [Ship Date]= #07/28/2014# THEN 1 //Holiday

          ELSEIF [Order Date]=#12/25/2014# OR [Ship Date]= #12/25/2014# THEN 1 //Holiday

          ELSEIF [Order Date]=#01/06/2015# OR [Ship Date]= #01/06/2015# THEN 1 //Holiday

          ELSEIF [Order Date]=#01/27/2015# OR [Ship Date]= #01/27/2015# THEN 1 //Holiday

          ELSEIF [Order Date]=#03/27/2014# OR [Ship Date]= #03/27/2014# THEN 1 //Holiday

          ELSEIF [Order Date]=#07/28/2015# OR [Ship Date]= #07/28/2015# THEN 1 //Holiday

          ELSEIF [Order Date]=#12/25/2015# OR [Ship Date]= #12/25/2015# THEN 1 //Holiday

          ELSE 0

          END

           

          Then another calculation (call it Excluding Weekends & Holidays) using your current calc of [Excluding Weekends]:

           

          ATTR([Excluding Weekends])-ATTR([Holidays])

           

          Hope it helps! Thx, Don

          • 2. Re: How to Exclude Holidays and Weekends in Tableau
            karthik Reddy

            Hi Don,

             

            thank you for response. i applied above calculation but it is calculating only the order date ship date is presented in that holidays then it is calculating the difference, if the Holidays is not present on Exact order date and Ship date(in between dates ) then it is not calculating the difference.

             

            Example:The below example 1/27/2014 is presented in holidays list so it is excluding and calculating the difference displaying result as 2 but when the same thing in 1/28/2014 it is ignoring the holiday date and calcuting the difference then displaying the result as 4(ideally it should be display as 3).

             

             

            Thank you..

            • 3. Re: How to Exclude Holidays and Weekends in Tableau
              Don Wise

              Hi,

              That's why I believe the other option will work best for you from the KB article. It's a much cleaner and more understandable method of subtracting out your Weekend and Holiday dates. Thx!  Don