Hey guys. I have a task under calculated measure creation. Lets say I need to gather all the workdays under status ‘In Progress’, during specific period of time.
The main problem here, is that if the transition occurs, the amount of workdays can exceed the limits of selected timeframe (since an issue could have been moved to ‘In progress’ status very long time ago). In addition, ticket could be left under In progress status for a long time too. So I need to solve puzzle:
How many days an issue spent under the first transition from In progress status (without exceeding the time frame) +
All the inner transitions from ‘In progress’ status that perfectly fits under timeframe +
All the timing that ticket sticks to ‘In progress’ status, without leaving it (currently).
I have made a plan, however, I am not sure how exactly apply operations to members, so that I am able to identify if the transition I am working with is the {first / in between /last one} under the selected time frame.
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:
If you also want to include the current status days, you can define a new account-specific field with those settings.
// 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");
