Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Santhosh Kumar Yerramally
    Exclude weekends from the create date and closed dateAnswered
    Topic posted December 11, 2018 by Santhosh Kumar YerramallyBlue Ribbon: 750+ Points 
    290 Views, 76 Comments
    Title:
    Exclude weekends from the create date and closed date
    Summary:
    Need to exclude the weekends while calculating he date difference
    Content:

    Hi Team,

    I am struggling to calculate the time difference between incident creation and closed dates by excluding the weekends. here are the columns and function which I am using to calculate date difference date_diff(incidents.closed,incidents.created), but this logic is counting the days including the weekends. As per requirement I need to exclude the weekends and calculate actual business dates. Could you please help me here on this issue.

    Thanks & Regards,

    Ashok Ganesh

    Version:
    18C

    Best Comment

    Santhosh Kumar Yerramally

    Got it. Thanks for the help Sebastiaan.

    I am going to make it answered the question :)

     

    Thanks & Regards,

    Ashok.

    Comments Are Closed

    • Neil

      The way I've achieved this previously is to user the 'rel_date_diff' function. To do this, you'll need to create a set of 'response requirements', which sit within SLAs. Your response requirements would probably need to be Mon-Fri 0000hrs-2359hrs.

      • Santhosh Kumar Yerramally

        Hi Neil,

        But as per business I have to keep business hours from 7 AM to 5 PM Mon - Fri. So is there any other alternative way to overcome this issue.

         

        Thanks,

        Ashok.

        • Neil

          You can set the response hours to M-F 0700-1700 to achieve this.

          Be aware that the 'rel_date'diff' calculation may differ to what exactly you're trying to achieve. It will return the difference in WORKING hours (well...'seconds'!).

          e.g. a rel_date_diff of Friday 1655hrs and Monday 0705hrs would be 600 (i.e. ten minutes) - rather than 2 days ~14hrs.

          Hope that makes sense.

           

           

          • Santhosh Kumar Yerramally

            Hi Neil,

             

            My requirement is not to calculate the actual working hours, I actually need to calculate the no of days from when the incident was created and when its closed by excluding the Saturday, Sunday and holidays. 

             

            Thanks,

            Ashok.

    • Sebastiaan Draaisma

      Neil is correct. An alternative might be to use this formula
      Helpful Formula - Business Days Calculation, Date_Diff

    • Sebastiaan Draaisma

      Hi Ashok.

      Please have a look at the attached sample report You can include or exclude todays date in your calculation. The hard coded holiday excemption can also be replaced by a more advanced ROQL holiday check

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        I have used below formula as you suggested but not getting exact day count.

        Formula: ((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - ((to_number(date_format(incidents.closed,'WW')) - to_number(date_format(incidents.created,'WW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0)

         

        Please refer attached screenshot for better understanding, as per screenshot the record was created on 01/02/2018 and closed on 02/02/2018 so the day count is around 24 excluding weekends. But with the above formula I am getting 2 hours 10 min. Could you please help me here with the formula.

         

        Thanks,

        Ashok.


         

    • Sebastiaan Draaisma

      Sorry for the late reply. I missed this reply.

      I did a quick test but it seems to work fine for me with the report I attached in the previous post.
      I changed sysdate() to incidents.closed since that seems what you are after.

      Depending if you want to include the end day in your counting (created today & answered today = 0 day or 1 day) you could use:

      ((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - ((to_number(date_format(incidents.closed,'WW')) - to_number(date_format(incidents.created,'WW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0)

      or

      ((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - ((to_number(date_format(incidents.closed,'WW')) - to_number(date_format(incidents.created,'WW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0) - 1

      The reason you see 2h 10m instead of 2.10 days is because you have formatted your column into a time format. Set this back to decimal and you will see the correct values.

      To have a round number you can use the round() function

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        Thanks a lot for the updates. With ((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - ((to_number(date_format(incidents.closed,'WW')) - to_number(date_format(incidents.created,'WW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0) logic I am getting the data as expected.

        But for few records the count is showing up weirdly like I mentioned in the below screenshot.

        As per screenshot it has to show around 24 days but it is showing 130 days. Could you please explain me why its showing like that and also I have mentioned the screenshot which is working properly.

         

        Thanks & Regards,

        Ashok.

         

    • Sebastiaan Draaisma

      An alternative is to use the rel_date_diff in combination with a custom script and perform the calculations that normally can not be done inside PHP.

      In the Process tab

      // 3600 seconds in an hour
      // 8 hour workday (change the code if you have different workdays specified)

      $sec = 8 * 3600;

      $rows[0][4]->val = $rows[0][3]->val / $sec;

      You can then 'hide' the column containing the seconds by changing it's width to 10 pixels and playing with borders through column formatting as removing the column would break the code.

    • Sebastiaan Draaisma

      A third alternative is to use the inc_performance table (type 4)

    • Sebastiaan Draaisma

      Would you be able to upload your exported report definition?

    • Santhosh Kumar Yerramally

      Hi All,

       

      Any help here?

       

      Thanks,

      Ashok.

    • Sebastiaan Draaisma

      Hi Ashok.

      It will be easier if you are able to upload your report definition for us to inspect.

    • Sebastiaan Draaisma

      Thanks Ashok.

      I will have a look at it later tonight :-)

    • Sebastiaan Draaisma

      Hi Ashok.

      I had a quick look at your expressions and see you are using
      date_diff(incidents.initial_soln,incidents.created)

      This could explain why some of your incidents have a higher number than others.
      Initial solved does not not always equal 1st answer.

      incidents.initial_soln

      The date and time that the first response that caused the status to change to a solved or waiting status type was sent to the contact

      If your status (incidents.status_id) equals an unresolved status type (incidents.status_type) this will not receive any date when sending an answer. It could then look like you are very bad at answering in time but it's just that the initial solved only receives a time stamp when status type is solved or waiting (not status id which could show resolved in an unresolved status type)

      To have the 1st answer I always use a combination of both inc_performance.time_end and incidents.initial_soln
      (in combination with inc_performance.intv_type = 4 on an outer join as to include incidents that have not yet received a date)

      nvl(inc_performance.time_end,incidents.initial_soln)

      Please observe that the inc_performance table is updated once per day so that changes made today may not be visible until tomorrow. The above expression nvl() checks if there is a date available for the inc_performance.time_end and when not, it looks for a date in the incidents.initial_soln

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        The reason why I am using date_diff(incidents.initial_soln,incidents.created) is that the client want to calculate the days when the initial solution has been provided. Also need to display the final day count when it was closed with the final resolution for the issue. 

        This is why I am displaying two separate columns one which calculate day count with initial close date and other column calculate the day count for the final resolution.

        Hope I have answered for the question.

         

        Thanks & Regards,

        Ashok.

    • Sebastiaan Draaisma

      I understand the requirement. It is however affecting your results, this as you measure initial solved. At least you will be able to explain this to the client as it is their requirement :-)

    • Sebastiaan Draaisma

      The only fix is to change the status type for your status from unresolved to a resolved or a waiting status as only resolved and waiting will generate a date for the incidents.initial_soln when sending an answer. Changing status type can affect other things such as lifecycle settings (purge_delete) and age db rules so you will have to inspect this before changing status type. The only other alternative is to report on another date.

    • Sebastiaan Draaisma

      I forgot to mention that you are unable to change a status type once it has been created meaning you will have to create a new status type with the same name so retroactive reporting will be a problem.

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        Thanks for the heads up on the issue. I will explain the problem to the customer and come back to you :)

        Thanks,

        Ashok.

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        I have raised SR and got the reason why the count is not coming properly and below is the explanation that I got from the SR.

         

        In January 2018, all dates between 1-6 jan are considered part of week 53 as the first Sunday of the year was the 7th of January.
         
        in the expression we are using these calculations (based on WW date token format):
         to_number(date_format(incidents.created,'WW')) which equals 53. (as the incident was created before 7th of Jan)
        to_number(date_format(incidents.initial_soln,'WW')) which equals 4. (February 2nd).
         
        the entire expression below becomes:
         ((to_number(date_format(incidents.initial_soln,'WW')) - to_number(date_format(incidents.created,'WW')))* 2) equals -98.
         the rest of the expression is correct.
         
        Any thoughts here?
         
        Thanks,
        Ashok.
    • Sebastiaan Draaisma

      Hi Ashok.

      You could try another week format
      See this link: Date and time format tokens for analytics

      IWEEKS

      Rounds the entered date value to the beginning of the week, using Monday as the week’s start. For example, Thursday July 16, 2009 = 07/13/2009.

      This token can be used only with the date_add and date_trunc functions.

      January 1st 2018 was a Monday

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        I have achieved the issue with "IW" format and below is the latest expression which is working as expected.

        " round(((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - ((to_number(date_format(incidents.closed,'IW')) - to_number(date_format(incidents.created,'IW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0),0) "

        Also I would like to exclude holidays from the business days, any help on that?

         

        I appreciate the continuous help here from you on the issue :)

         

        Thanks & Regards,

        Ashok.