Sum DateDiffWorkhours on a specific status

Hi there,

The workflow in my context allows issues to be analyzed multiple times (same status). I need to get all the working hours on this specific status for one of my SLAs.

If an issue is analyzed twice or less, I can use DateDiffWorkhours() combined with [Measures].[Transition to status first date], [Measures].[Transition from status first date], [Measures].[Transition to status last date] and [Measures].[Transition from status last date] and it works like a charm.

But if the issue is analyzed three times or more, I’m missing information in eazyBI Server.

I know how to import the “missing dates” in eazyBI (table or string in one JavaScript calculated custom fields) but I don’t know how to use them now.

Any idea on your side ?

Thanks for yout feedback !

Hi Thomas,
​Importing multiple timestamps as comma-separated values into a property, unfortunately, might not solve your request.
​Although they might be available, the MDX does not have the proper construction to cycle through the timestamps and calculate the interval length.
​The solution might be to create a JavaScript calculated custom field that finds the time spent in specific status and processes that to retrieve working hours only.
​You might find the common JavaScript code for the workhours function here - Assignment hrs work days eazybi.
​After adding the code for the workhours function, the code to retrieve workhours spent in a specific status and register time against the date of exiting status might be as follows.

name="Workhours in status"

var isWorked = false;
var forRecord = false;
var duration = 0;
var currStartDate = null;
var oldStartDate = null;
var result = new Array();

var workStatuses = ["In Progress"]

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 (workStatuses.indexOf(item.toString) > -1) {
                        isWorked = true;
                        forRecord = false;
                    } else if (workStatuses.indexOf(item.toString) == -1) {
                        isWorked = false;
                        forRecord = true;
                  if (isWorked && !forRecord) {
                     currStartDate = history.created;}
                  else if (!isWorked && forRecord) {
                    oldStartDate = currStartDate;
                    currStartDate = history.created;
                    duration =  workinghours ( oldStartDate , currStartDate );
                    if (duration >0){
                    result.push( currStartDate.substr(0,10)+", "+ duration);}
                    forRecord = false;
    issue.fields.customfield_statWorkHours = result.join("\n");

​Oskars /

1 Like

Hi Oskars,
Thanks for your feedback and sorry for the late reply. This is not the solution we will adopt for various reasons but it works!