B2C Servicepublic

Reporting and Analytics for Service

Get Involved. Join the Conversation.

This is a public Forum  public

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 
    338 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

    • Sebastiaan Draaisma

      Hi Ashok.

      Glad you got it working :-)
      When it comes to excluding holidays there is no easy way (at least not, if you want this dynamic). I don't know how skilled you are with PHP? If you are skilled with PHP than it might be easier as this will al have to be done through a custom script.

      As the holiday table normally is not accessable in analytics this data will be retrieved through ROQL after which you have access to the data. Have a look at the attached report that will show you how the report is build.

      If you are new to custom script and new to PHP than this may proof to be challenging. I have created a 'How to' for beginners in custom script.

      A 'How to' on using regular expressions in custom scripts for GDPR

      There might be other ways to do this but this is one example (also in the previous attached report) where you would need to repeat this for every holiday.

      ((DATE_DIFF(sysdate(),incidents.created)/86400) + 1) - ((to_number(date_format(sysdate(),'WW')) - to_number(date_format(incidents.created,'WW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(sysdate(),'DAY') = 'Saturday',1,0) - 1 - if(incidents.created<'2018-05-02' & sysdate()>'2018-05-01',1,0) - if(incidents.created<'2018-04-03' & sysdate()>'2018-04-02',1,0)

      To have this dynamic you will most likely have to rebuild your entire report and perform all calculations in PHP (custom script) in the exit tab. Like I said, this is unfortunately going to take some time and programming but you will learn a lot by doing this.

      When importing the attached report you will be asked if you would like to import the report without custom script, here you will click No as you do want to import the custom script.

      2.4.3 Holidays 100405.xml (16KB)
      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        I am able to exclude the holidays in a static way and thanks a lot for your suggestion.

        Also I have another observation here, i.e the current logic will excludes weekends and holidays only for the incidents which were raised and closed in the same year.

        For example: If any incidents was raised in 2017 and closed in 2018 then I am getting weird results.

        It would be great help here and its the final fix which holding me to complete the report.

        Here is the expression which is working properly for current year " ((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) "

         

        Thanks in advance...:)

        Ashok.

         

    • Sebastiaan Draaisma

      Hi Ashok

      You could try to experiment with this expression:

      if(date_format(incidents.closed,'YYYY') > '2017',to_date('20171231','YYYYMMDD'),incidents.closed)

      What it does is it will check if the incidens.closed year is higher than 2017 and if so, it will use 31st of December 2017 as a date and otherwise it will use the incidens.closed date.

      This expression would basically replace every place you are currently using incidents.closed

      To have this dynamic you could use:

      if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed)

      In a report this would look like this.

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        I have tried the above expression by incorporating into my logic but its not giving me the proper count. could you please help me here?

        Here is the modified logic:

        " round(((DATE_DIFF(incidents.initial_soln,incidents.created)/86400) + 1) -
        ((to_number(date_format(if(date_format(incidents.initial_soln,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1)
          ,to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.initial_soln),'IW')) - to_number(date_format(incidents.created,'IW')))* 2) - 
        IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.initial_soln,'DAY') = 'Saturday',1,0),0)
        "

        Thanks & Regards,

        Ashok.

    • Sebastiaan Draaisma

      Try this one:

      ((DATE_DIFF(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),incidents.created)/86400) + 1) - ((to_number(date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'IW')) - to_number(date_format(incidents.created,'IW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'DAY') = 'Saturday',1,0)

      To include the round you can use:

      round(((DATE_DIFF(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),incidents.created)/86400) + 1) - ((to_number(date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'IW')) - to_number(date_format(incidents.created,'IW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'DAY') = 'Saturday',1,0),0)

    • Sebastiaan Draaisma

      I think a better way would be to reverse the date_iff so that the calculation reflects the true value. Let me test a little and get back to you :-)

    • Sebastiaan Draaisma

      Try this one :-)

      ((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),((to_number(date_format(incidents.created,'IW')) - 52)* 2),((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)

      • Santhosh Kumar Yerramally

        No Luck with this also. can you see the second record it was created on 27th Dec 2017 and closed on 1st Jan 2018 so the day count should be 4 by excluding weekends, but it is giving the count with weekends too.

    • Sebastiaan Draaisma

      Yeah, I saw it... smiley
      The solution lays in counting the weeks after new year and multiply this by 2 (Sat + Sun) that will be extracted.

      I modified the code (v3). It looks correct now :-)

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

      Code explained: If the incident was created in the same year as it was closed, it will perform the regular calculation (week nr - week nr) to get the total weeks * 2. ELSE IF the incident was closed in the year > current system date year - 1 it will only count the week number the incident was closed * 2 (to get Sat + Sun) and extract this from the total.

      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        Still no luck..Its failing to current year incidents, I have attached report definition please verify once. I will try from my end by manipulating the logic.

         

        Thanks,

        Ashok.

        New Report -101.xml (17KB)
        • Sebastiaan Draaisma

          Your report contained an older code.

          Try the the one in the previous post :-)

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

          • Santhosh Kumar Yerramally

            Hi Sebastiaan,

             

            I am close to the results now able to get the proper results for current year incidents but for previous year incidents it is giving 3 days extra for each incident. Please verify the highlighted incidents below.

             

            Thanks,

            Ashok.

    • Sebastiaan Draaisma

      No what is happening is that January 19th is in week 3 and than it would count that as 3 x 2 days. In other words 6 days extraction

    • Sebastiaan Draaisma

      That is the difference between using IW & WW

    • Sebastiaan Draaisma

      Hi Ashok.

      The negative numbers are most likely incidents created on a Sunday or closed on a Saturday. If the previous calculation returned a 0 extracting 1 would become -1

      - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0)

    • Sebastiaan Draaisma

      Why not use the rel_date_diff aproach Ashok, it will make things so much easier for you (and me) laugh
      It will take care of your holidays and weekends, everything according to your SLA settings

      See attached report (and previous post December 17)
      You only need to specify your opening hours In the Process tab (which in the attached report is set to 24 as my client is open 24H)

      // 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;

      rel_date_diff 100520.xml (19KB)
    • Sebastiaan Draaisma

      Ah, yes. That is because you have formatted your column into a time format. You should specify this as decimal type as the conversion is done through PHP (custom script). If you have difficulties getting this to work than just upload your desired report and specify where you want to display this and I will configure it for you. Right now I'm leaving the house but I will be back in a couple of hours.

      Download and import my report to see how it works :-)

    • Sebastiaan Draaisma

      Did you adjust the opening hours 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;

      How many hours do you have a working day? Than I can adjust this in your report (almost done)

    • Sebastiaan Draaisma

      Attached is your report with the following custom script (adjusted to a 10 hour workday)

      Code:

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

      $sec = 10 * 3600; //10 hours

      $initial = $rows[0][0]->val / $sec;    // rel_date_diff Initial solved
      $closed = $rows[0][1]->val / $sec;    // rel_date_diff Closed

      $rows[0][10]->val = round($initial);    // Round
      $rows[0][11]->val = round($closed);    // Round

      $rows[0][10]->val = preg_replace("/(-\d+)/", "", $rows[0][10]->val); // Remove negative numbers (when there is no initial solved date)
      $rows[0][11]->val = preg_replace("/(-\d+)/", "", $rows[0][11]->val); // Remove negative numbers (when there is no closed date)

      As I don't have your custom field I created this into a comment (just remove the single quotes ' ' from the source column)
      'incidents.c$incident_source'

      Report Ashok 100521.xml (47KB)
      • Santhosh Kumar Yerramally

        Hi Sebastiaan,

         

        Its working now smiley.

         

        I am so much glad and thankful to your help on this, I have learnt so many things from you. I will keep on testing the report in different ways and give the updates.

        If things are proper then I will make the question as answered and we will keep in touch on further issues wink

        Thanks & Regards,

        Ashok.

    • Sebastiaan Draaisma

      Glad I could help Ashok

      Best wishes for 2019 and don't forget to select a best answer so the topic will be marked as resolved (shrinks the list) smiley

    • Sebastiaan Draaisma

      Absolutely :-)

      Instead of calculating the seconds during your 10 opening hours:

      $sec = 10 * 3600; //10 hours

      You would just devide it by the seconds in 1 hour

      $sec = 3600; //1 hour in seconds

      The rel_date_diff in seconds would than be devided by the seconds in an hour giving you the hours.
      My advice when you are new to custom script is to follow my little tutorial, it will get you started in custom scripts which are easier than you may think :-)

      It will also teach you how the columns are counted. Hidden columns give you trouble that is why I simply add them and adjust the width so you don't notice them.

    Comments Are Closed

    community forum