Assignment hrs work days eazybi

Questions & Answers
2

Hi @agupta,

When eazyBI calculates the duration in working days, it takes into account the day of the week and holidays but does not know when working hours start and end (https://docs.eazybi.com/eazybijira/data-import/jira-issues-import#JiraIssuesImport-Timedimension).

We do have improvement on the eazyBI backlog to support calculations based on business hours, but I can not estimate when it will be developed and available.

Meantime, Javascript calculated custom fields or scripted fields in Jira is the option to calculate such a sophisticated result by working days and business hours. Below is an example of JavaScript calculated custom field for resolution time in working hours.

  1. eazyBI allows defining some functions you can use within any JavaScript code for Jira data import. You might want to define a new function datediffworkhours() that would calculate duration between two timestamps and take into account working days of the week as well as at what time the working day starts and ends. The definition for function datediffworkhours() for working day from 8:00 to 17:00 (line 12 and 13 in code) and working week from Monday to Friday (line 11 in code) might look like this:

    [jira]
common_javascript_code = '''
// workhours function
function datediffworkhours( from, to ) {
  var c1 = new Date(Date.parse(from));
  var c2 = new Date(Date.parse(to));  
  var days = 0.0;
  var workhours = 0;
  var fullWeeks = 0;
  var nonworkCount = 0;
  var nonworkdays = [0,6];
  var hoursfrom = 8;
  var hoursto = 17;
  workingdayhours = hoursto - hoursfrom;
  // check that from date is before to date
  if (c1 > c2) return -datediffworkhours(to, from );
  // get hours from first day
  w1 = c1.getDay();
  h1 = c1.getHours() + c1.getMinutes() / 60;
  if (nonworkdays.indexOf(w1) < 0) {
    if(h1 <= hoursfrom) {
      workhours += workingdayhours;
    } else if(h1 <= hoursto) {
        workhours += hoursto - h1;
      } else {
          workhours += 0;
        }
    // move to next day
    c1.setHours(0);
    c1.setMinutes(0);
    c1.setSeconds(0);
    c1.setMilliseconds(0);
    c1.setDate( c1.getDate() + 1 );
  }
  // get hours from last day
  w2 = c2.getDay();
  h2 = c2.getHours() + c2.getMinutes() / 60;
  if (nonworkdays.indexOf(w2) < 0) {
    workhours -= workingdayhours;
    if(h2 <= hoursfrom) {
      workhours += 0;
    } else if(h2 <= hoursto) {
        workhours += h2 - hoursfrom;
      } else {
          workhours += workingdayhours;
        }
    // move to next day
    c2.setHours(0);
    c2.setMinutes(0);
    c2.setSeconds(0);
    c2.setMilliseconds(0);
    c2.setDate( c2.getDate() + 1 );
  }
  // move from date to first workday
  while (true) {
  w1 = c1.getDay();
  if (nonworkdays.indexOf(w1) > -1 ) {
    c1.setHours(0);
    c1.setMinutes(0);
    c1.setSeconds(0);
    c1.setMilliseconds(0);
    c1.setDate( c1.getDate() + 1 );
   } else{break;}
  }
  // move to date to first workday
   while (true) {
    w2 = c2.getDay();
    if (nonworkdays.indexOf(w2) > -1 ) {        
      c2.setHours(0);
      c2.setMinutes(0);
      c2.setSeconds(0);
      c2.setMilliseconds(0);
    c2.setDate( c2.getDate() + 1 );;
    } else{break;}
   }
   days = (c2.getTime() - c1.getTime()) / 86400000.0;
   fullWeeks =  Math.floor(days / 7);
   nonworkCount = fullWeeks * nonworkdays.length;  
  //count nonworkdays in last week    
  c3 = new Date(c1.getTime() + fullWeeks * 7 * 86400000);
  while (c3 < c2) {
     w3 = c3.getDay();
      issue.customfield_w3 = w3;       
     if (nonworkdays.indexOf(w3) > -1)  {
      nonworkCount += 1;
    }
      c3.setDate( c3.getDate() + 1 );
  }
  return workingdayhours * (days - nonworkCount) + workhours;
}
'''

    Place this code in eazyBI advanced settings.

    Please read the documentation page for more details: https://docs.eazybi.com/eazybi/data-import/data-adjustments-using-javascript#DataadjustmentsusingJavaScript-CommonJavaScriptcodeforJiradataimport.

  2. Define a new JavaScript calculated custom field that calculates duration in working hours between two issue dates you are interested. For example, to calculate resolution time in working hours (duration from creation time till resolution time), the advanced settings with JavaScript might look like this:

    [jira.customfield_resolvedwh]
name = "Resolution time in work hours"
data_type = "decimal"
measure = true
multiple_dates = true
javascript_code = '''
if (issue.fields.resolutiondate) {
    var created = issue.fields.created;
    var resolved = issue.fields.resolutiondate;
    var wh = datediffworkhours (created , resolved) ; 
    issue.fields.customfield_resolvedwh = resolved.toString().substr(0,10) + "," + wh;
}
'''

    Place this code in eazyBI advanced settings after the definition of function datediffworkhours().

    Please read the documentation on how to create JavaScript calculated custom fields and where to place the code: https://docs.eazybi.com/eazybijira/data-import/custom-fields/javascript-calculated-custom-fields.

  3. In eazyBI import options, tab Custom fields, select “Resolution time in work hours” for data import and import data.

Best,
Zane / support@eazyBI.com

1 Like