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

      Sure, no problem. With custom scripts whenever you add a column you may (not always) have to change the code (depending on where you place the column) as the column ID in the code will then no longer reflect the column where you want your data.

      If you could place the Hour columns where you want to have them and upload your report than I will adjust the code for you.

    • Sebastiaan Draaisma

      Please find the modified version attached with the following code

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

      $sec = 10 * 3600; //10 hours

      $initial_d = $rows[0][0]->val / $sec;    // rel_date_diff Initial solved days
      $initial_h = $rows[0][0]->val / 3600;    // rel_date_diff Initial solved hours
      $closed_d = $rows[0][1]->val / $sec;    // rel_date_diff Closed days
      $closed_h = $rows[0][1]->val / 3600;    // rel_date_diff Closed hours

      $rows[0][10]->val = round($initial_d);    // Round
      $rows[0][11]->val = round($initial_h);    // Round
      $rows[0][12]->val = round($closed_d);    // Round
      $rows[0][13]->val = round($closed_h);    // Round

      $rows[0][10]->val = preg_replace("/(-\d+)/", "", $rows[0][10]->val); // Remove negative numbers
      $rows[0][11]->val = preg_replace("/(-\d+)/", "", $rows[0][11]->val); // Remove negative numbers
      $rows[0][12]->val = preg_replace("/(-\d+)/", "", $rows[0][12]->val); // Remove negative numbers
      $rows[0][13]->val = preg_replace("/(-\d+)/", "", $rows[0][13]->val); // Remove negative numbers

      Please observe that these are relative hours (hours counted durring your opening hours)
      If rounding is not required you do not need to use any custom script. In that case you could use the rel_date_diff in your column and format the column into a time format with the source specified as seconds.

      Report Ashok v2 100522.xml (52KB)
    Comments Are Closed

    community forum