Long Loading Times for Report with "Average time to response" Formula

Hello everyone,

We have an issue with a report we created. Initially it worked well, but recently we are experiencing longer loading times up to a point where we keep getting error messages and I have to reload the page several times before the table is even displayed.

Background: We are using Jira to handle Google- and Apple App Store Reviews. Each incoming review triggers a Jira ticket to be automatically created. Our organization users can write responses to the app store reviews from Jira itself.

One metric we are concerned with are the average working days from when the ticket is being created to when the first answer is being given.

For that purpose we created a calculated member “Avg. time to review response”. We were able to validate the results, but it seems to require a lot of memory causing the abovementioned issue. You will find the formula below. “Review response send” is the transition status the ticket passes when the Jira user writes a App Store response – “Waiting for NSC” is the very first status given to the ticket once it is created.

NonZero(

Avg(

Filter(

Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),

(

[Measures].[Transitions to status],

[Transition Status].[Review Response Send]

)>0

),

Datediffworkdays(

(

[Measures].[Transition to status first date],

[Transition Status].[Waiting for NSC],

[Time].CurrentHierarchy.DefaultMember

),

(

[Measures].[Transition to status first date],

[Transition Status].[Review Response Send]

)

)

)

)

Do you have any idea why this is happening or whether the KPI can be calculated differently?

Hi,

this calculation can have performance problems because it iterates the full set of issues for each cell where the measure is calculated.

The optimization is possible using a Javascript-calculated custom field in eazyBI.

The following Javascript code shall be used:

Javascript code
function workdays ( from, to ) {
  var c1 = new Date(Date.parse(from));
  var c2 = new Date(Date.parse(to));  
  var days = 0.0;
  var fullWeeks = 0;
  var nonworkCount = 0;
  nonworkdays = [0,6];
  // till there
   
  // check that from date is before to date
//   if (c1 > c2) return -workdays(to, from, nonworkdays);
  // move from date to first workday  
  while (true) {
  w1 = c1.getDay();
  if (nonworkdays.indexOf(w1) > -1 ) {
    c1.setHours(0);
    c1.setMinutes(0);
    c1.setSeconds(0);
    c1.setMilliseconds(0);
    c1.setDate( c1.getDate() + 1 );
  }else{break;}
}
  // move to date to first workday
   while (true) {
    w2 = c2.getDay();
    if (nonworkdays.indexOf(w2) > -1 ) {        
      c2.setHours(0);
      c2.setMinutes(0);
      c2.setSeconds(0);
      c2.setMilliseconds(0);
    c2.setDate( c2.getDate() + 1 );;
    } else{break;}
   }
   days = (c2.getTime() - c1.getTime()) / 86400000.0;
   fullWeeks =  Math.floor(days / 7); 
   nonworkCount = fullWeeks * nonworkdays.length;  
  //count nonworkdays in last week    
  c3 = new Date(c1.getTime() + fullWeeks * 7 * 86400000);
  while (c3 < c2) {
     w3 = c3.getDay();
     if (nonworkdays.indexOf(w3) > -1)  {
      nonworkCount += 1;
    }
      c3.setDate( c3.getDate() + 1 );
  }
  return days - nonworkCount;
}

var start=null;
var end=null;
var result="";
var duration=null;
for (hist of issue.changelog.histories) {
  for (hist_item of hist.items) {
    if (hist_item.field=="status" && hist_item.toString=="Waiting for NSC" && !start) start=hist.created;
    if (hist_item.field=="status" && hist_item.toString=="Review Response Send" && !end) end=hist.created;
    if (start && end) {
      duration = workdays(start, end);
//       duration=(Date.parse(end)-Date.parse(start))/1000/3600/24;
      result=end.substr(0,10)+","+duration;
      break
    }
  }
}
return result;

After you import this custom field, the average can be calculated as a ratio:

[Measures].[Time to response]
/
([Measures].[Transitions to status issues count],
 [Transition Status].[Review Response Send])

Kindly,
Janis, eazyBI support

Hi Jannis, thank you so much for this.

this seems to be what I am looking for, but I still have one problem.

When I use your Java Script Code and test it for any issue it doesn’t return a value.

I think this has to do with the status “Waiting for NSC”. If I exchange this with the next status in the workflow “In Progress NSC” this does give me a result.

Looking at the history the default status of our issues is “Waiting for NSC”, but there isn’t a log entry specifying the date when this status is set.

Could this be why there is no value from the query? And is there a way around this by e.g. using the issue create date?

Thank you in advance!

Hi,

It looks like there is a specific case not covered with this Javascript, unfortunatley. Could you confirm that this issue was assigned to the Waiting for NSC status at the creation?

I will provide update of the Javascript if that is the case.

Kindly,
Janis

Hi Janis,

Yes, upon creation of a ticket, the first status is called Waiting for NSC.

Thank you,
Aqilah

Hi,

One more line of code was needed:

Summary
function workdays ( from, to ) {
  var c1 = new Date(Date.parse(from));
  var c2 = new Date(Date.parse(to));  
  var days = 0.0;
  var fullWeeks = 0;
  var nonworkCount = 0;
  nonworkdays = [0,6];
  // till there
   
  // check that from date is before to date
//   if (c1 > c2) return -workdays(to, from, nonworkdays);
  // move from date to first workday  
  while (true) {
  w1 = c1.getDay();
  if (nonworkdays.indexOf(w1) > -1 ) {
    c1.setHours(0);
    c1.setMinutes(0);
    c1.setSeconds(0);
    c1.setMilliseconds(0);
    c1.setDate( c1.getDate() + 1 );
  }else{break;}
}
  // move to date to first workday
   while (true) {
    w2 = c2.getDay();
    if (nonworkdays.indexOf(w2) > -1 ) {        
      c2.setHours(0);
      c2.setMinutes(0);
      c2.setSeconds(0);
      c2.setMilliseconds(0);
    c2.setDate( c2.getDate() + 1 );;
    } else{break;}
   }
   days = (c2.getTime() - c1.getTime()) / 86400000.0;
   fullWeeks =  Math.floor(days / 7); 
   nonworkCount = fullWeeks * nonworkdays.length;  
  //count nonworkdays in last week    
  c3 = new Date(c1.getTime() + fullWeeks * 7 * 86400000);
  while (c3 < c2) {
     w3 = c3.getDay();
     if (nonworkdays.indexOf(w3) > -1)  {
      nonworkCount += 1;
    }
      c3.setDate( c3.getDate() + 1 );
  }
  return days - nonworkCount;
}

var start=null;
var end=null;
var result="";
var duration=null;
for (hist of issue.changelog.histories) {
  for (hist_item of hist.items) {
    if (hist_item.field=="status" && hist_item.toString=="Waiting for NSC" && !start) start=hist.created;
    if (hist_item.field=="status" && hist_item.fromString=="Waiting for NSC" && !start) start=issue.fields.created;
    if (hist_item.field=="status" && hist_item.toString=="Done" && !end) end=hist.created;
    if (start && end) {
      duration = workdays(start, end);
//       duration=(Date.parse(end)-Date.parse(start))/1000/3600/24;
      result=end.substr(0,10)+","+duration;
      break
    }
  }
}
return result;

Kindly,
Janis, eazyBI support