Measuring SLAs of defects (bugs) in a structure

[Marc Dahinden]

Dear all

I like to share an use case w/ you I like to create in JIRA structure. I like to manage my defects (bugs) during hypercare reg. SLAs and use JIRA structure.

Use case:

The SLA of a bug w/ several priorities = "blocker" have SLAs (like 4 hours for a priority = "blocker"). I like to measure the SLA using creation date & time (create) and today's date and time (NOW() or TODAY()).
The formula should measure if SLA > than defined SLA per priority and post an appropriate text like "critical; roadblock > 4h". If bug is still within SLA then returning the text "within SLA" as a result.

I created the following formula in a column. 

IF(priority ="blocker" && ((Today() - created) > 4); "critical: roadblock > 4h"; 
   priority ="critical" && ((Today() - created) > 48); "critical: Delay for > 2 days";
   priority ="major" && ((Today() - created) > 96); "SLA of 4 days exceeded";
   priority ="minor" OR priority ="trivial" AND ((Today() - created) >
   120);"SLA of 5 days exceeded";
  "within SLA")

The formula is able to map the correct text per priority but the formula does not take into consideration if SLA is not exceeded (past time < SLA).
I have the impression that the formula does not know the unit of measurement (=hour) of SLA.
How would a solution look like?

Thanks, Marc

[David Niro]

Hello @Marc Dahinden ,

You are correct, the formula is not working due to formatting.  The way now() and created are stored in Jira are different than what we see in the UI.  Therefore we need to use the HOURS_BETWEEN() function to calculate the duration.

Below is a formula that should work for you, based on your example.  I made a couple of changes, saving now() as a variable, as well as the duration calculation itself.  These types of calculations can be heavy, so I wanted to make sure we are only doing them once for each issue.

with _now = now():
with _timesince = HOURS_BETWEEN(created,_now):

IF priority = "blocker" AND _timesince >4: 
    "critical: roadblock >4h"
ELSE IF priority = "critical" AND _timesince >48: 
    "critical: roadblock > 2days"
ELSE IF priority = "major" AND _timesince >96: 
    "SLA of 4 days exceeded"
ELSE IF (priority = "minor" or priority = "trivial") AND _timesince >120: 
    "SLA of 5 days exceeded"
ELSE: "within SLA"

Please give it a try and let me know if it works for you!

Best,
David

[Marc Dahinden]

Thanks David.

Sorry for my basic understanding of formulas.

Your proposal works fine!

May I request for a small additional requirement? I hope this is fine.

I like to use colors:
Red bgColor and white text for priority ="blocker" or "critical"

What would be the correct formula for this?

Best, Marc

[David Niro]

Hello @Marc Dahinden ,

You are very welcome and no apology is necessary!  I'm happy to help shed light on some of the nuances.

I'm glad to hear that it is working as expected for you.  I modified the formula to include this.  You will notice the new local variable (function actually) format_caption().  It defines the color of the background and text.  I then used it with the return value for blocker and critical priorities.

with _now = now():
with _timesince = HOURS_BETWEEN(created,_now):
with format_caption(caption)=
    """{panel:borderStyle=solid|borderColor=white|bgColor=red}{color:white}*$caption*{color}{panel}""":

IF priority = "blocker" AND _timesince >4: 
    format_caption("critical: roadblock >4h")
ELSE IF priority = "critical" AND _timesince >48: 
    format_caption("critical: roadblock > 2days")
ELSE IF priority = "major" AND _timesince >96: 
    "SLA of 4 days exceeded"
ELSE IF (priority = "minor" or priority = "trivial") AND _timesince >120: 
    "SLA of 5 days exceeded"
ELSE: "within SLA"

Please let me know if it helps!

Best,
David

[Marc Dahinden]

Hi David
Thanks for your proposal.

I get as result in the column:

{panel:borderStyle=solid|borderColor=white|bgColor=red}{color:white}*critical: roadblock >4h"{color}{panel}

It seems the code is interpretated as text.

Any idea why?

Best, Marc

[David Niro]

Hi @Marc Dahinden ,

You are very welcome!  I have a very good idea why. It was something I thought to communicate in my previous response, but failed to.

In the Formula window, you will see "Options".  Most likely, the drop down is set to "General" at the moment.  Changing this to "Wiki Markup" should format the background and text color.

Sorry for neglecting to mention it.

Best,
David

[Marc Dahinden]

Thanks, it works