4 Replies Latest reply on Sep 11, 2018 7:53 AM by Nisha Gala

    Business Dates calcuation

    Vijaya Pediredla

      I am having issue with business Dates calcuation on Tableau.Total number of iteration days. The below caluating is including weekends. How do i remove "Saturday" and "Sunday".

       

      DATEDIFF('day',[Iteration Startdate], [Iteration Enddate])

        • 1. Re: Business Dates calcuation
          Shawn Wallwork

          Here's the solution I use:

          DATEDIFF('day',[StartDate], [Date])  
          -  
          (DATEDIFF('week',DATETRUNC('week',[StartDate]),DATETRUNC('week',[Date]))) * 2  
          -  
          IIF(DATEPART('weekday', [Date]) = 1, 2, 0)  
          -  
          IIF(DATEPART('weekday', [Date]) = 7, 1, 0)  
          +  
          IIF(DATEPART('weekday', [StartDate]) = 1, 2, 0)  
          +  
          IIF(DATEPART('weekday', [StartDate]) = 7, 1, 0)
          
          

          Brought to you by James Baker.

           

          --Shawn

          • 2. Re: Business Dates calcuation
            Vijaya Pediredla

            Thanks a lot for your answer Shawn. It worked pretty well.

            • 3. Re: Business Dates calcuation
              Shawn Wallwork

              Yep, that Mr. Baker is a clever guy.

               

              --Shawn

              • 4. Re: Business Dates calcuation
                Nisha Gala

                Hi All,

                 

                I have similar need but in this case I am trying to calculate business days that has passed within the month. How do I do so?

                 

                I need them because I need to calculate the daily run rate of order and here are the steps/formula I have used which aren't really working well. In addition, is there a way to include holidays that falls into business day within the calculation as well. For example Sept 3 was a holiday in the USA and hence is not a valid business day.

                 

                1. Days Current Month = (DATEPART('day', TODAY() ))

                2. avg days curr month= AVG([Days Current Month])

                3. Weekend Days = If [avg days curr month] >= 28  Then 8

                ELSEIF [avg days curr month] >=21 Then 6

                ELSEIF [avg days curr month] >=14 Then 4

                ELSEIF [avg days curr month] >=7 Then 2

                ELSEIF [avg days curr month] = 6

                AND ([Day of Week] = "Friday" OR [Day of Week] = "Saturday")

                Then 1

                ELSEIF [avg days curr month] = 6

                AND ([Day of Week] <> "Friday" AND [Day of Week] <> "Saturday")

                Then 2

                ELSEIF [avg days curr month] = 5

                AND ([Day of Week] = "Thursday" OR [Day of Week] = "Saturday")

                Then 1

                ELSEIF [avg days curr month] = 5

                AND ([Day of Week] = "Sunday" OR [Day of Week] = "Monday"

                OR [Day of Week] = "Tuesday" OR [Day of Week] = "Wednesday")

                Then 2

                ELSEIF [avg days curr month] = 5

                AND ([Day of Week] = "Friday" )

                Then 0

                ELSEIF [avg days curr month] = 4

                AND ([Day of Week] = "Wednesday" OR [Day of Week] = "Saturday")

                Then 1

                ELSEIF [avg days curr month] = 4

                AND ([Day of Week] = "Sunday" OR [Day of Week] = "Monday"

                OR [Day of Week] = "Tuesday")

                Then 2

                ELSEIF [avg days curr month] = 4

                AND ([Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

                Then 0

                ELSEIF [avg days curr month] = 3

                AND ([Day of Week] = "Tuesday" OR [Day of Week] = "Saturday")

                Then 1

                ELSEIF [avg days curr month] = 3

                AND ([Day of Week] = "Sunday" OR [Day of Week] = "Monday"

                )

                Then 2

                ELSEIF [avg days curr month] = 3

                AND ([Day of Week] = "Wednesday" OR [Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

                Then 0

                ELSEIF [avg days curr month] = 2

                AND ([Day of Week] = "Monday" OR [Day of Week] = "Saturday")

                Then 1

                ELSEIF [avg days curr month] = 2

                AND ([Day of Week] = "Sunday")

                Then 2

                ELSEIF [avg days curr month] = 2

                AND ([Day of Week] = "Tuesday" OR [Day of Week] = "Wednesday" OR [Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

                Then 0

                ELSEIF [avg days curr month] = 1

                AND ([Day of Week] = "Sunday" OR [Day of Week] = "Saturday")

                Then 1

                ELSEIF [avg days curr month] = 1

                AND ([Day of Week] = "Monday" OR [Day of Week] = "Tuesday" OR [Day of Week] = "Wednesday" OR [Day of Week] = "Friday" OR [Day of Week] = "Thursday" )

                Then 0

                ELSE 0 END

                4. Business Days = [Days Current Month] - [Weekend Days]

                5. Business Days Reported = [Business Days]-1

                6. Daily Curr Month Order Rate = (SUM([Curr Mth Bookings])/[Business Days Reported])