BusinessDays/WorkHours-based SLA calculation

[Esteban Borja Lopez]

disclaimer - I cannot use the SLA default functionality for this exercise so implementing it via automation and custom fields

Hello community!

I'm trying to set different SLA times by comparing creation date of a given ticket vs the timestamp of a transition to a certain status... some calculation use calendar days (24/7) and some use business days (8/5). I need the result to be returned in minutes  (as some SLAs counts e.g. 15 mins for response time since ticket creation).

For 24/7 ones Im using the following formula on an if-else block, and triggering an actions based on the result:

• {{issue.created.diff(now).minutes}}

While for 8/5 Im using the following formula:

• {{issue.created.diff(now).businessDays.multiply(1440)}} 

The problem Im struggling with is that I need to work with concrete numbers like "SLA: 8 hours since ticket creation" and workdays/hours calendar as Im not able to hit the right formula to calculare exact elapsed time since ticket creation until the condition is met (e.g. status change).

Example:  considering working days is Mon to Fri, from 9am to 5pm, for a ticket created at 3pm on a Friday, if the SLA is 8 hours, due datetime should be next monday at 3pm.

In addition, I would like to know if it's possible to set national/local holidays into the formula (configurable).

The resulting number will be compared against the defined SLA.

Thanks in advance for your time/help!

[Bill Sheboy · Accepted solution]

Hi @Esteban Borja Lopez 

First thing, please check your site settings for your business hours to confirm the start/end of the business day. 

Next, you appear to be multiplying by 1440 which is the number of minutes in 24 hours, not 8 or 9 hours, or however long your work day is by the settings.

Regarding the holidays, I believe those (for projects settings) have no impact on the built-in business day checks for automation, so you would need to manually test for those dates with repeated conditions, or try some list filtering/incrementing.  I recommend searching for other posts trying to do this as I recall reading some indicating this is not straightforward to implement.

Finally, have you thought of this approach: when an issue is created use your expected SLA to set the Due Date field to {{issue.created.plusBusinessDays(yourSLAValue)} and then use another rule with a scheduled trigger and JQL to detect issues where the Due Date has lapsed past now?

Kind regards,
Bill

[Esteban Borja Lopez]

Hey @Bill Sheboy, 

thanks for taking the time on reading/answering my post.

On regards your points:

1. I've asked IT admin to check the config related to workdays and working hours. Will come back once having feedback. Question: Is this what you called " built-in business day checks for automation" something implicit on global config I can use on my calculations? (e.g. when calling "BusinessDays" function it checks the general calendar config to know what is business days).

2. Yes, the 1440 is because when comparing 2 dates (datetime format) using business day, if the full day is not yet completed, the result of the calculation is 0 which leads me to error on the further formulas. By using the 1440 I define that the condition is met within 1 business day (in mins to keep consistency with other KPIs).

3. About your proposal, yes, I've tried out a similar approach. But how to set SLAs based on minutes or hours instead of days... Im not sure if it covers the various scenarios Im dealing with. Examples (working days: Mon / Fri from 9am to 6pm):

1. Case 1:
   1. SLA response time since issue creation: 30 mins (only on business hours)
   2. Ticket is opened on a Tuesday at 5:50pm
   3. Due date for SLA should be Wednesday 9:20am (10 mins from Tue and 30 mins from Wed)
2. Case 2: 
   1. SLA response time since issue creation: 4 hours (only on business hours)
   2. Ticket is opened on a Tuesday at 4pm
   3. Due date for SLA should be Wednesday 11am (2 hours from Tue and 2 hours from Wed)
3. Case 3:
   1. SLA resolution time since issue creation: 20 hours (only on business hours)
   2. Ticket is opened on a Friday at 3pm
   3. Due date for SLA should be Wednesday 10am (5 hours from Fri, 8 hours from Mon, 8 hours from Tue and 1 hour from Wed)

If Im able to get an accurate result out of this, I can easily compare/manage accordingly as you are suggesting... but so far couldn't make it work.

Regards,

Esteban

[Esteban Borja Lopez]

I made it work by using the proposed approach (by setting sth like "{{issue.created.plusBusinessHours(1).minusMinutes(30)}}" for 30 mins within business hours)

Thanks for the feedback @Bill Sheboy !

[Bill Sheboy]

Replacing my response...

I did not know there was a plusBusinessHours() feature.  Well done!

[Esteban Borja Lopez]

neither I did... nor even that several logic can be combined in same calculation (like plus and minus).

FYI - I cannot use default SLA functionality as a) I want consistency across multiple projects and units (internal and external), something I get as some projects has been created not as service project but development ones, b) while fetching JIRA data from Confluence via nested macros, some fields are seen but no value on them despite I can see them fulfilled on JIRA

[Bill Sheboy]

For the missing data in the Confluence macros, I recommend working with your site admin to submit a ticket to Atlassian Support as they may not know about that symptom: https://support.atlassian.com/contact/#/

[Deepika]

@Esteban Borja Lopez I am working on a similar use case, but instead of populating custom field, I am triggering email notifications if the SLA has missed, 

Can you explain the formula you have used for 30 min SLA calculation with an example?
{{issue.created.plusBusinessHours(1).minusMinutes(30)}}

[kshridvipa7878]

Hi, @Esteban Borja Lopez @Bill Sheboy 

Hope you both are doing well!

I am working on similar use cases:

1. Case 1:
   1. SLA response time since issue creation: 30 mins (only on business hours)
   2. Ticket is opened on a Tuesday at 5:50pm
   3. Due date for SLA should be Wednesday 9:20am (10 mins from Tue and 30 mins from Wed)
2. Case 2: 
   1. SLA response time since issue creation: 4 hours (only on business hours)
   2. Ticket is opened on a Tuesday at 4pm
   3. Due date for SLA should be Wednesday 11am (2 hours from Tue and 2 hours from Wed)
3. Case 3:
   1. SLA resolution time since issue creation: 20 hours (only on business hours)
   2. Ticket is opened on a Friday at 3pm
   3. Due date for SLA should be Wednesday 10am (5 hours from Fri, 8 hours from Mon, 8 hours from Tue and 1 hour from Wed)

If you could provide smartvalue and conditions you used to implement above three cases, it will be relay helpful for me.

I am strangling a lot since last week but finding it very difficult to implement this automation rule, Fortunately I came across this discussion today and seeking for your help now.

Thank you for your time and consideration, looking forward for your response on this query, 

Thank you,

Dvipa

[Bill Sheboy]

Hi @kshridvipa7878 

As this is a very old thread, I recommend creating a new question, and perhaps linking back to this one.  That will ensure the maximum number of people see your question to offer ideas.

When you create your question, please include specifics for:

• the problem you are trying to solve; that is, "why do this"
• the Atlassian products you have available: Jira, JSM, etc.
• an image of your complete rule
• an image of the audit log details showing the rule execution
• what is not working as expected
• what you have tried thus far to resolve the problem

Kind regards,
Bill

[kshridvipa7878]

Hi @Bill Sheboy 

Thank you for your suggestion. 

I have created a new questions. 

Thank you,

Dvipa