3 Replies Latest reply on May 12, 2017 4:00 PM by Mark Bradbourne

    Calculating the number of Ship Days in Month

    Wesley Turner

      Hi 

      I am attempting to have Tableau count the number of ship days in a month. A ship day according to my company is all weekdays in a month excluding national holidays in the United States. An example of this is... In May there are 23 Weekdays but only 22 Ship days because of Memorial Day. Is there a function or calculation that I could use to accomplish this task.  I


      Thank you 

       

      PS If this can be done without adding another Data Source that would be ideal.

        • 1. Re: Calculating the number of Ship Days in Month
          Michael Someck

          Hey Wesley,

           

          Here's a KB article that you might find useful. The second section is business days=weekdays excluding holidays. Unfortunately, to exclude holidays, I think you'll need an excel sheet with a list of what your company considers a holiday. Hope this helps!

           

          Michael

          • 2. Re: Calculating the number of Ship Days in Month
            Mark Bradbourne

            The other option would be to code to check for the holidays :

            ---- New Years Day (1/1/(YEAR)
            ---- Martin L King's Birthday ( 3rd Monday in January )
            ---- Presidents Day ( 3rd Monday in February )
            ---- Memorial Day ( Last Monday in May )
            ---- Independence Day ( July 4 )
            ---- Labor Day ( 1st Monday in September )
            ---- Columbus Day ( 2nd Monday in October )
            ---- Veterans Day ( November 11 )
            ---- Thanksgiving Day ( 4th Thursday in November )
            ---- Christmas Day ( December 25 )
            ---- New Years Eve Day

            The trick will be when the holidays fall on Saturday or Sunday and they shift to the Friday or Monday... If I get bored over the weekend I might actually take a stab at figuring this out,

            • 3. Re: Calculating the number of Ship Days in Month
              Mark Bradbourne

              Here's the quick and dirty version, not shifting to Mondays/Fridays.

               

              [Work Date Flag]

              IF (

              (DATENAME('weekday',[Order Date]) = 'Saturday' or DATENAME('weekday',[Order Date]) = 'Sunday') //Weekends

              OR (DAY([Order Date]) = 1 and MONTH([Order Date]) = 1) // New Years Day

              OR (Month([Order Date]) = 1 and DAY([Order Date])>=15 AND Day([Order Date])<=21 AND DATENAME('weekday',[Order Date])='Monday') // MLK's Birthday

              OR (Month([Order Date]) = 2 and DAY([Order Date])>=15 AND Day([Order Date])<=21 AND DATENAME('weekday',[Order Date])='Monday') // President's Day

              OR (Month([Order Date]) = 5 and DAY([Order Date])>=25 AND Day([Order Date])<=31 AND DATENAME('weekday',[Order Date])='Monday') // Memorial Day

              OR (DAY([Order Date]) = 4 and MONTH([Order Date]) = 7) // July 4th

              OR (Month([Order Date]) = 9 and DAY([Order Date])>=1 AND Day([Order Date])<=7 AND DATENAME('weekday',[Order Date])='Monday') // Labor Day

              OR (Month([Order Date]) = 10 and DAY([Order Date])>=9 AND Day([Order Date])<=15 AND DATENAME('weekday',[Order Date])='Tuesday') // Columbus Day

              OR (DAY([Order Date]) = 11 and MONTH([Order Date]) = 11) // Veterans Day

              OR (Month([Order Date]) = 11 and DAY([Order Date])>=22 AND Day([Order Date])<=28 AND DATENAME('weekday',[Order Date])='Thursday') // Thanksgiving Day

              OR (DAY([Order Date]) = 24 and MONTH([Order Date]) = 12) // Christmas Eve

              OR (DAY([Order Date]) = 25 and MONTH([Order Date]) = 12) // Christmas Day

              OR (DAY([Order Date]) = 31 and MONTH([Order Date]) = 12) //New Years Eve Day

              )

              Then 'No'

              Else 'Yes'

              End

               

              Then create a calc that that counts distinct when the date calc is "yes"

              [Work Days]

              IF [Work Day Flag] = "Yes" then COUNTD([Order Date])

              End