Assignment hrs work days eazybi

agupta · July 30, 2020, 2:18pm

Can we get somehow the average hours or the assignment hours workdays which exclude non working hours .

Regards
Aakanksha

zane.baranovska · August 25, 2020, 6:55am

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.

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.

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.
In eazyBI import options, tab Custom fields, select “Resolution time in work hours” for data import and import data.

Best,
Zane / support@eazyBI.com

coagulant · April 28, 2022, 7:32am

Looks like DateDiffWorkhours is mentioned in the documentation: DateDiffWorkhours but anvailable in eazybi as of 6.4.1. could you elaborate?

gerda.grantina · April 28, 2022, 8:41am

@coagulant the new function is available on Cloud and will be available on eazyBI version 6.5. (not yet released)

zane.baranovska · September 26, 2022, 3:33pm

On Cloud and since eazyBI version 6.5 (already released), there is a new MDX function DateDiffWorkhours() that calculates the duration between two dates in working hours.
For more details please see the documentation: DateDiffWorkhours

Best,
Zane / support@eazyBI.com

Marcelo_Ignacio_Cid1 · May 9, 2024, 4:15pm

Hi @zane.baranovska

I want to be able to average the time a ticket spent in a state according to my work days (work days in transition status) but giving a time interval according to my work day.

Ex: Let’s think that the PR-123 ticket was in the In Progress state for a total of 20 days (Monday to Friday) if my work schedule is from 8am to 6pm, what I want to know is how long the ticket was effective given that schedule, That is to say, “let’s think” that it was 11 days so I can know that it was 20 days in the in progress state but it was really 11 days that the team worked on.

I understand that the code provided gives the solution for a specific work schedule, but what if I have two work schedules for two different people?

Any ideas on how I can do this, either via Java or through the eayzbi console.