Workdays in specific status under predefined timeframe (period)

[gerda.grantina]

Hi @Ignas,
Welcome to the eazyBI support!

If I understand you correctly, you want to see your workdays in status split by the specified time frames. Here is an example from my colleague Janis Plūme for the JavaScript custom field that creates a new measure to show the split of the Time by months:

Time that tickets stayed in a specific status during a specific period

Hi, The default measure of Days in transition status does not split the days by the period selected in the report. Instead, it shows the full number of days spent in the status for the period when the issue transitioned from the status. It is possible to create a workaround with a Javascript calculated custom field. I used the following advanced settings for the solution: [jira.customfield_wd_inst_by_month] name="Workdays in status by months" data_type="decimal" measure=true multiple_dimensi…

If you also want to include the current status days, you can define a new account-specific field with those settings.

and use this JavaScript code:

// Function to calculate fractional days between two dates
function fractionalDaysBetween(from, to) {
  var milliseconds = to - from;
  return milliseconds / (1000 * 60 * 60 * 24);
}

// Function to get the start of the day
function getStartOfDay(date) {
  return new Date(date.getFullYear(), date.getMonth(), date.getDate());
}

// Function to get the last day of the month
function getLastDayOfMonth(date) {
  return new Date(date.getFullYear(), date.getMonth() + 1, 0, 23, 59, 59, 999);
}

// Function to add or update a result entry
function addResult(date, status, days) {
  var key = strftime("%Y-%m-%d", date) + "," + status;
  if (resultObj[key]) {
    resultObj[key] += days;
  } else {
    resultObj[key] = days;
  }
}

var datefrom = new Date(Date.parse(issue.fields.created));
var resultObj = {};
var currentDate = new Date();

issue.changelog.histories.forEach(function(history) {
  history.items.forEach(function(historyItem) {
    if (historyItem.field === "status") {
      var statusfrom = historyItem.fromString;
      var dateto = new Date(Date.parse(history.created));
      var dayStart = getStartOfDay(dateto);
      
      // Calculate fraction of the transition day for the previous status
      var fractionForPreviousStatus = fractionalDaysBetween(dayStart, dateto);
      
      if (datefrom.getMonth() === dateto.getMonth() && 
          datefrom.getFullYear() === dateto.getFullYear()) {
        // Same month
        var lastDayOfMonth = getLastDayOfMonth(datefrom);
        var endDate = dateto <= lastDayOfMonth ? dateto : lastDayOfMonth;
        var totalDays = fractionalDaysBetween(datefrom, endDate);
        addResult(endDate, statusfrom, totalDays);
      } else {
        // Spanning multiple months
        while (datefrom < dayStart) {
          var lastDayOfMonth = getLastDayOfMonth(datefrom);
          var endDate = lastDayOfMonth > dayStart ? dayStart : lastDayOfMonth;
          addResult(endDate, statusfrom, fractionalDaysBetween(datefrom, endDate));
          datefrom = new Date(endDate.getFullYear(), endDate.getMonth() + 1, 1);
        }
        // Add the fraction of the transition day for the previous status
        if (fractionForPreviousStatus > 0) {
          addResult(dateto, statusfrom, fractionForPreviousStatus);
        }
      }
      
      // Update datefrom to the exact time of the status change
      datefrom = new Date(dateto.getTime());
    }
  });
});

// Add the current status duration only if the issue is not resolved
var currentStatus = issue.fields.status.name;
if (!issue.fields.resolution) {
  if (datefrom.getMonth() === currentDate.getMonth() && 
      datefrom.getFullYear() === currentDate.getFullYear()) {
    // Same month
    var lastDayOfMonth = getLastDayOfMonth(currentDate);
    var endDate = currentDate <= lastDayOfMonth ? currentDate : lastDayOfMonth;
    addResult(endDate, currentStatus, fractionalDaysBetween(datefrom, endDate));
  } else {
    // Spanning multiple months
    while (datefrom < currentDate) {
      var lastDayOfMonth = getLastDayOfMonth(datefrom);
      var endDate = lastDayOfMonth > currentDate ? currentDate : lastDayOfMonth;
      addResult(endDate, currentStatus, fractionalDaysBetween(datefrom, endDate));
      datefrom = new Date(endDate.getFullYear(), endDate.getMonth() + 1, 1);
    }
  }
}

// Convert the result object to an array and format the output
var result = Object.keys(resultObj).map(function(key) {
  var parts = key.split(',');
  return parts[0] + "," + parts[1] + "," + resultObj[key].toFixed(2);
});

// Return the result as a string
return result.join("\n");

The result would be like this:

If you need any adjustments, you can write directly to support@eazyBI.com or, if you are on eazyBI for Jira Cloud, you can try our new JavaScript custom field AI Assistant: Introducing AI Assistants in eazyBI

Best,
Gerda // support@eazyBI.com