I’m trying to use DateDiffWorkhours to show me the cycle time in working hours, as by working day is pointless.
I have two custom Issue Cycles defined, to measure time in Active workflow statuses and time in dwell workflow statuses.
I have tried various approaches of makeing a calculated field that I expect to return my a duration of time between two dates, where time in status is within the defined working hours.
Both approaches below, show ‘No report data to show’ in a table where I selected them as a calculated measure.
It would be really useful!! to be able to inspect what the functions are returning, to help debug what’s going on.
DateDiffWorkhours([Measures].[Issues Dwell Cycle Time started],
[Measures].[Issues Dwell Cycle Time ended], ‘default’, ‘9-17’)
DateDiffWorkHours(
[Issue].get(‘Issue Dwell Cycle Time start date’),
[Issue].get(‘Issue Dwell Cycle Time end date’),‘default’,‘9-17’)
I also tried using Jira fields to see if I could get this working and still got no results.
DateDiffWorkHours(
[Issue].get(‘Created at’),
[Issue].get(‘Resolved at’),
‘default’,
‘0-23’)
Hi @Thomas_Roberts,
Your expressions are based on the properties of an issue.
You need to have a distinct issue available for the specific expression, and the easiest way is to add the Issue dimension to the report rows.
However, the issue cycle start and end properties would return the start of the first cycle and the end of the last cycle. The result is only usable if the issue goes through each cycle once.
If the issue exits the cycle and continues after a while, the results would be wrong.
You might use a JavaScript calculated customfield to retrieve the workhours spent in a set of statuses.
First, you might take the workhours function code from this community post - Assignment hrs work days eazybi - #2 by zane.baranovska.
Then you might adapt the following script to return time in cycles split by dates, assignees, and priorities with the following script.
Please read more about defining new custom field here - JavaScript calculated custom fields.
[jira.customfield_spentTime]
name="Hours open by assignee"
data_type="decimal"
measure=true
multiple_dimensions=["Time","Assignee", "Priority"]
javascript_code='''
var isWorked = false;
var forCount = false;
var forRecord = false;
var duration = 0;
var currStartDate = null;
var oldStartDate = null;
var result = new Array();
var openStatuses = ["In Progress", "Open"];
var firstAssignee = "";
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
var histories = issue.changelog.histories;
for (var i = 0; i < histories.length && !firstAssignee; i++) {
var history = histories[i];
if (history.items && history.items.length > 0) {
for (var n = 0; n < history.items.length && !firstAssignee; n++) {
var item = history.items[n];
if (item.field == "assignee") {
firstAssignee = item.from;
if (!firstAssignee) {firstAssignee = "(unassigned)";}
}
}
}
}
}
if (!firstAssignee && issue.fields.assignee) {firstAssignee = issue.fields.assignee.key;}
var oldAssignee = firstAssignee;
var currAssignee = oldAssignee;
var firstPrio = "";
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
var histories = issue.changelog.histories;
for (var i = 0; i < histories.length && !firstPrio; i++) {
var history = histories[i];
if (history.items && history.items.length > 0) {
for (var n = 0; n < history.items.length && !firstPrio; n++) {
var item = history.items[n];
if (item.field == "priority") {
firstPrio = item.from;
}
}
}
}
}
if (!firstPrio && issue.fields.priority) {firstPrio = issue.fields.priority.id;}
var oldPrio = firstPrio;
var currPrio = oldPrio;
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
var histories = issue.changelog.histories;
for (var i = 0; i < histories.length; i++) {
var history = histories[i];
if (history.items && history.items.length > 0) {
for (var n = 0; n < history.items.length; n++) {
var item = history.items[n];
if (item.field == "status") {
if (openStatuses.indexOf(item.toString) > -1) {
isWorked = true;
forCount = true;
forRecord = false;
} else if (openStatuses.indexOf(item.toString) == -1 && isWorked) {
isWorked = false;
forCount = true;
}
}
else if (item.field == "assignee") {
oldAssignee = currAssignee;
currAssignee = item.to;
if (!currAssignee) {currAssignee = "(unassigned)";}
forRecord = true;
}
else if (item.field == "priority") {
oldPrio = currPrio;
currPrio = item.to;
forRecord = true;
}
if (isWorked && forCount) {
currStartDate = history.created;
forCount = false;
}
if ((isWorked && forRecord)||(!isWorked && forCount) ) {
oldStartDate = currStartDate;
currStartDate = history.created;
var begTime = Date.parse(oldStartDate);
var endTime = Date.parse(currStartDate);
var begDate = Math.floor(begTime/86400000);
var endDate = Math.ceil(endTime/86400000);
for (var j = begDate; j < endDate; j++) {
var currDate = new Date( j*86400000);
var dayEnd = new Date(Date.parse(currDate)+86400000);
if(Date.parse(currDate) < Date.parse(oldStartDate)){
duration = workinghours ( oldStartDate , dayEnd );
}
else if (Date.parse(dayEnd) > endTime){
duration = workinghours ( currDate , currStartDate );
}
else {
duration = workinghours( currDate , dayEnd );
}
if (duration >0){
var regDate = currDate.toISOString();
result.push( regDate.substr(0,10)+ ","+ oldAssignee + ","+oldPrio + ","+ duration);}
}
oldAssignee = currAssignee;
oldPrio = currPrio;
forRecord = false;
forCount = false;
}
else if (!isWorked && forRecord) {
oldStartDate = currStartDate;
currStartDate = history.created;
oldAssignee = currAssignee;
oldPrio = currPrio;
forRecord = false;
}
}
}
}
if(result){
issue.fields.customfield_spentTime = result.join("\n");
}
}
'''
Regards,
Oskars / support@eazyBI.com