4 Replies Latest reply on Aug 17, 2017 11:51 AM by Saurabh Mohanty

    Calculating public holidays between two different dates

    Priyanka V

      Hi everyone,

       

      I'm stuck in a very bad situation with the public holidays calc. I have two different dates "Open Date" and "Close Date" and need to calculate turnaround time between these two dates excluding public holidays and weekends.

       

      What I'm up to so far:

       

      I have calculated the turn around using a date diff.

      DATEDIFF('day',[Opened Time (Closed Cases)],[Closed Time])

       

      However, I was able to get it right only after using a LOD

       

      i.e, { FIXED [Case Number]: MAX([Days (Case Turnaround)])}

       

      I don't understand why! Now I need to calculate the public holidays+weekends between open date and closed date and exclude them from case turnaround.

       

      In Order to calculate 'Public Holidays & Weekends', I have a date table in the data base there are following fields:

      • all dates in chronological order
      • Is weekend flag
      • Is weekday flag
      • Is national holiday flag

       

      I have used these flags to calculate the count of public holidays and weekends

       

      Public holiday Count:

       

      IF [DATE_DT]>=[Opened Time (Closed Cases)]
      AND
      [DATE_DT]<=[Closed Time]
      AND
      [IS_HOLIDAY_NATIONAL_YN]='Y'
      THEN 1
      ELSE 0
      END

       

      Weekend Count:

       

      IF [DATE_DT]>=[Opened Time (Closed Cases)]
      AND
      [DATE_DT]<=[Closed Time]
      AND
      [IS_WEEKEND_YN]='Y'
      THEN 1
      ELSE 0
      END

       

      This doesn't work, even after fixing it to case number level. I have gone through all the posts of public holidays but have been unsuccessful so far. Please help.

       

      Thanks

        • 1. Re: Calculating public holidays between two different dates
          Norbert Maijoor

          Priyanka!

           

          Thanks for reaching out and sharing your challenge.

           

          Find below link as a "starter"...

           

          Calculating the Number of Business Days | Tableau Software

          • 2. Re: Calculating public holidays between two different dates
            Shinichiro Murakami

            Difficulty here is we cannot make relationships between two tables.

            This is not fantastic way, but you can do something.

            Only brings the result of

            - Number of Days between start and end excluding Weekend and Holidays for specific Event

             

            Another concern is ugly parameter name..., anyway

             

            Create Calculated field in the event table.

            [EventID_Date]

            [Event ID]+"_"+str(Year([Start])*10000+month([Start])*100+day([Start]))+

            str(Year([End])*10000+month([End])*100+day([End]))

             

            Create parameter using above [EventID_Date]

            Then , decompose [EventID_Date] to [Event Name], [Event_Start], and [Event_End].

            [Event Name]

            left([event_select],find([event_select],"_")-1)

             

            [Event_Start]

            makedate(

            int(mid([event_select],find([event_select],"_")+1,4)),

            int(mid([event_select],find([event_select],"_")+5,2)),

            int(mid([event_select],find([event_select],"_")+7,2))

            )

             

            [Event_End]

            makedate(

            int(mid([event_select],find([event_select],"_")+9,4)),

            int(mid([event_select],find([event_select],"_")+13,2)),

            int(mid([event_select],find([event_select],"_")+15,2))

            )

             

            Now you can calculate number of days for respective selected Event through parameter.

             

            Other way to achieve the goal is that you create all the combination of [Event] x [Date] as different table.

            (See Sheet Table_3 case)

             

            Prepare three tables

            Table 1 = Event start / end

            Table 2 = Holiday Calendar

            Table 3 = All [Event] x [Date] combination which Includes all "day" between event start and event end.

            As Table 3 primary data make relationships like.

            Table 3 Event =link= Table 1 Event

            Table 3 Date =link= Table 2 Date

             

            This solution is much easier in case you can easily create Table 3.

             

            Thanks,

            Shin

            • 3. Re: Calculating public holidays between two different dates
              Kyle Boyce

              I have a questions in regards to calculating Business Days. I've made a Workdays Days Completed Calculated field using:

              DATEDIFF("weekday", [Start Date], [End Date])

              - 2 * (DATEPART('week', [End Date]) -DATEPART('week', [Start Date]))

              + (IF DATENAME('weekday',[End Date]) = 'Saturday' OR DATENAME('weekday',[Start Date]) = 'Sunday'

              THEN 0 ELSE 1 END)

               

              The above gives me the number of workdays between the Start Date (MIN(date)) and the End Date (TODAY()-1), BUT I need to figure out the number of workdays in the month that I'm currently in.

               

               

              • 4. Re: Calculating public holidays between two different dates
                Saurabh Mohanty

                I don't know if any one has found a solution yet. But I can give what I have done for this situation

                I have a holiday excel with dates listed down. I have a custom query which gives me start date and end date.

                in order to check  how many holidays are in between a start date and end date my approach will be as follows.

                 

                Define a dummy column in excel which acts as a join column and give value as 1 . Then in custom query have a join columnn defined with same value..

                 

                Join the two data sources like shown in diagram