Days for assignee custom field

hello eazybi team,

i imported the custom field “days for assignee” in eazybi and have some doubts that the outcome is correct.
i need in fact the following:
if an issues is created it goes through some states until it is closed. inbetween the assignees’ change also within the states (means status = new but assignee might change from A to B and back to A and so on). what i need is the cummulated time an issue was assigned to a specific user until a ticket is solved or even better the time until now.
when i use the days for assignee custom field it evaluates only the “solved” tickets and the numbers are not correct, there are to less issues listed.
can someone help me here or is there another way to show what i need?

the following code i used for the custom field:

[jira.customfield_days_for_assignee]
name = “Days for assignee”
data_type = “decimal”
measure = true
multiple_dimensions = [“Time”,“Assignee”]
javascript_code = ‘’’
var assigneehistory = new Array();
var datefrom = issue.fields.created;
var assignee = null;
resolution = false;

issue.changelog.histories.forEach(function(history){
history.items.forEach(function(historyItem){
if (historyItem.field == “assignee” && ! resolution) {
assignee = historyItem.from;
dateto = history.created;
if(assignee){
duration = (Date.parse(dateto) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
assigneehistory.push(dateto.toString().substr(0,10) + “,” + assignee + “,” + duration);
}
datefrom = dateto;
assignee = historyItem.to;
}
if (historyItem.field == “resolution”) {
if (historyItem.to) resolution = true;
else resolution = false;
}
});
});

if (issue.fields.resolutiondate && (issue.fields.assignee || assignee)) {
if (!assignee) assignee = issue.fields.assignee.name;
duration = (Date.parse(issue.fields.resolutiondate) - Date.parse(datefrom)) / 1000 / 60 / 60 / 24;
assigneehistory.push(issue.fields.resolutiondate.toString().substr(0,10) + “,” + assignee + “,” + duration);
}

issue.fields.customfield_days_for_assignee = assigneehistory.join(“\n”);
‘’’

thanks in advance,

philipp

The JavaScript custom field calculates days for any assignee for any open and resolved issues. We calculate the time when an assignee was removed, or an issue was resolved.

The formula added to our demo account has one slight problem for assignee changes close to resolution. We updated the formula in our documentation for it. However, I am not sure I got your problem correctly.

Here is more information on how it works:
This measure does not count how long an issue is assigned to a current assignee for open issues since the last assignment.

You can calculate the last date of an assignment for any issue and assignee with this formula, though:

CASE WHEN 
  [Assignee].CurrentMember.Level.Name = "User" AND
   [Issue].CurrentMember.Level.Name = "Issue" AND
   Cast([Assignee].CurrentMember.Key as String) = CoalesceEmpty([Issue].CurrentHierarchyMember.GetString("Assignee name"),"")
THEN
  TimestampToDate((
     [Measures].[Transition to last timestamp],
     [Transition field].[Assignee],
     [Time].CurrentHierarchy.DefaultMember
  ))
END

The formula Days for assignee and the one above does not take into account statuses changes as well. Measure Days for assignee calculates a time till issue resolution date was set, though.

If you would like to see a cumulative time for any assignee how long it took to work on it (still does not check the statuses, only the fact the issue is open), here is an example calculation for this:

Case when
  DateAfterPeriodEnd(
    "Today",
    [Time].CurrentHierarchyMember)
  OR 
  DateInPeriod(
    "Today",
    [Time].CurrentHierarchyMember
  )
THEN
NonZero(AVG
(Filter(
 Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
     IIF([Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
     1,
 DateBeforePeriodEnd(
   [Issue].CurrentHierarchyMember.Get('Created at'),
   [Time].CurrentHierarchyMember)
 AND
 ( isEmpty( [Issue].CurrentHierarchyMember.get('Resolved at'))
 OR
   DateAfterPeriodEnd(
   [Issue].CurrentHierarchyMember.Get('Resolved at'),
   [Time].CurrentHierarchyMember.PrevMember)))
 ),
 Case when
 -- for resolved issue use the calculted value
   DateInPeriod( 
     [Issue].CurrentHierarchyMember.get('Resolved at'),
     [Time].CurrentHierarchyMember)
 THEN
 -- for any open period when an issue was assigned:
   CASE WHEN
   [Measures].[Issues history] >= 0 THEN
   NonZero(([Measures].[Days for assignee],
   [Time].CurrentHierarchy.DefaultMember)) 
   END
 WHEN
   [Measures].[Issues history] > 0
 Then
 -- days how long issue has been in for this assignee assigned so far if the issue is assigned to the assignee at the end of the period
 DateDiffDays(
  -- the date when issue transits to this status
       Cache(TimeStampToDate(MAX(
    {PreviousPeriods([Time].CurrentHierarchyMember),
     [Time].CurrentHierarchyMember},
     ([Measures].[Transition to last timestamp],[Transition Field].[Assignee])
    ))),
   TimeStampToDate(IIF([Time].CurrentHierarchyMember
     is [Time].CurrentHierarchy.DefaultMember, 
      DateToTimeStamp([Time].CurrentHierarchy.Levels('Day').CurrentDateMember.NextStartDate),
      DateToTimeStamp([Time].CurrentHierarchyMember.NextMember.StartDate))))
 +
     -- cumulative days in transition so far
 Cache(SUM(
    {PreviousPeriods([Time].CurrentHierarchyMember),
     [Time].CurrentHierarchyMember},
     [Measures].[Days for assignee]
     ))
 when
 -- for any open period when issue was removed from an assignee pick imported value
  [Measures].[Issues history] = 0
 Then
 -- cumulative days for this assignee so far issue who transits from this assignee in period
  Cache(SUM(
    {PreviousPeriods([Time].CurrentHierarchyMember),
     [Time].CurrentHierarchyMember},
     [Measures].[Days for assignee]
  ))
 End
))
END

The formula is quite complex and can work extremely slow or timeout in account with a large issue set or complex reports.

If you are in any doubt that formula does not work as expected you can check out our training on how to debug formulas:
How to see through calculated measure

Daina / support@eazybi.com

Hi @Philipp ,
I wanted to let you know that we have released eazyBI version 7.0 recently. In this version, we included the new predefined measures for “Days assigned”, “Workdays assigned”, “Average days assigned” and others. See more information about the new measures here: Import issue change history

Please see a list of all changes: Changelog - eazyBI for Jira.

best,
Gerda // support@eazybi.com