### Are you in the loop? Keep up with the latest by making sure you're subscribed to Community Announcements. Just click Watch and select Articles.

×
cancel
Showing results for
Did you mean:
See all
##### Top groups
Explore all groups

## Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user

Level 1: Seed

25 / 150 points

Next: Root

## Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

What goes around comes around! Share the love by gifting kudos to your peers.

## Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

# Measuring SLAs of defects (bugs) in a structure

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

1 vote
David Niro
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Jan 02, 2023

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

Thanks David.

Sorry for my basic understanding of formulas.

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
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Jan 03, 2023

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

Hi David

I get as result in the column:

It seems the code is interpretated as text.

Any idea why?

Best, Marc

David Niro
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Jan 03, 2023

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

Dave Rosenlund likes this

Thanks, it works

# people like this