Reduce calculation time for query

Questions & Answers
1

Hi,
we are currently building our sla report and are using the timetosla app. We have one SLa where we calculate if a ticket is transitioned from the status open to any other status in the first 7 days after the ticket is created. To get the total number of tickets which are relvant for last months report we use the issue measure from timetosla but for it to work we need to include the tickets which are currently still in status open and older than 7 days

We created the following script:
Sum(
Cache(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateDiffDays([Issue].CurrentMember.GetProperty(“Created at”), Now()) > 7
)),
[Measures].[PROB_Ticket Processed Issues] +
([Status].[Open], [Issue Type].[Problem])
)

PROB_Ticket Processed is the name of the SLA.

The problem is that this measure runs into a timeout probably because it checks to many tickets… Are there any options to reduce the time to calculate the tickets?
Thx
Simon

2

Hello @skolbe,

Thanks for posting your question!

Since you are only looking at one Issue type and status, we suggest using the following formula. It will first check if the Issue matches the Issue type “Problem” and is in an Open status, then filter by age and return the Issues that match all of the criteria.

Sum(
  Filter(
    Descendants(
     [Issue].CurrentMember,
     [Issue].[Issue]),
--filter by issue type
   [Measures].[Issue type]= "Problem"
   AND
--filter by issue status
   [Measures].[Issue status] = "Open"
   AND
--filter by issue age
   DateDiffDays([Issue].CurrentMember.GetProperty("Created at"), Now()) > 7
  ),
--related to context
 [Measures].[Issues created]
)

I hope this helps!

Best,
Marita / support@eazybi.com

3

HI marita,

thank you for the help we will test this out and get back to you

thanks
SImon