How can I calculate the contractual penalties for issues that are in SLA breached?

I would like to reproduce the following table and create the formula for calculating penalties:

SLA

Type SLA A is within 4 working days, on the fifth day the penalty is 80 €, from the sixth day the penalty is 50 €
Type SLA C is next business day, from the second day the penalty is 130 €, from the third day the penalty is 100 €
Type SLA B is within 10 working days, on the eleventh day the penalty is 80 €, from the twelfth day the penalty is 50 €

below the calculations made for each type of SLA:
Type SLA A: (80x1 day)+(50x2,71 days)= 215,5 €
Type SLA C: (130x1 day)+(100x3,67 days)= 497 €
Type SLA B: (80x1 day)+(50x23,50 days)= 1255 €

Regards
Francesco

Can someone help me thanks?

Hi @Fra!

I am sorry for the delay in providing you with an answer!

Firstly, to calculate how many days are over the target, it would be necessary to know the targets. eazyBI does not import SLA target hours as separate property as JIRA does not provide this information for all states during a cycle.

There is an option to use JavaScript calculated field to get the goal where possible during import (replace NNNNN with the SLA custom field ID). The following works on the server app, if you are looking to do this on Jira Cloud, please try to add the advanced settings and let me know if you have problems importing it.

[jira.customfield_sla1_goal]
data_type="decimal"
name = "SLA 1 goal"
javascript_code = '''

var hours=0.0;

if (issue.fields.customfield_NNNNN){
	cCycles = issue.fields.customfield_NNNNN.completeCycles;
	if (cCycles) {
		hours=cCycles[0].goalTime/1000/3600;
	} else {
		oCycle=issue.fields.customfield_NNNNN.ongoingCycle;
		if (oCycle) {
			hours = (oCycle.elapsedTime+oCycle.remainingTime)/1000/3600;
		}
	}
}
if (hours>0) {

issue.fields.customfield_sla1_goal=hours;}
'''

After importing, you can use the target property in a report. See a similar report with Time to resolution target breached:

Additionally to the default measures, there are two formulas used:

1 Days over target subtracting the target from elapsed hours

Cache(
CASE WHEN 
  [Measures].[Issue Time to resolution target] < 
  [Measures].[Time to resolution Elapsed hours]
THEN 
  ([Measures].[Time to resolution Elapsed hours] -
  [Measures].[Issue Time to resolution target]) / 8 -- divide hours by 8 to get days
END)

2 Penalty, which for the IP-48 issue calculates as 130 per first three days and 100 for the remaining 0.17 days

Sum(
  Filter(Descendants(
    [Issue].CurrentMember, [Issue].[Issue]
  ), [Measures].[Time to resolution - Breached] > 0 AND
     [Measures].[Time to resolution Elapsed hours] > 0), -- get all breached issues with Elapsed hours
CASE 
WHEN [Measures].[Issue Time to resolution target] = 8 -- use target as SLA group. If 8h, then
THEN
  CASE WHEN [Measures].[Days over target] < 1
    THEN 80 * [Measures].[Days over target]
  ELSE 80 + ([Measures].[Days over target]-1) * 50
  END
WHEN [Measures].[Issue Time to resolution target] = 40 -- if target is 40h, then 130 penalty for first three days and 100 for the rest of days
THEN
  CASE WHEN [Measures].[Days over target] < 3
    THEN 130 * [Measures].[Days over target]
  ELSE 130*3 + ([Measures].[Days over target]-3) * 100
  END
END
)

Lauma / support@eazybi.com

1 Like

Hi @lauma.cirule
thanks for your help, I appreciated.
In the JIRA service desk project we created “SLA TT REALE” under SLAs which targets 23h Goal.
In the workflow when an issue goes from “Assigned” to “Waiting for supplier” starts the SLA hours count, the SLA hours count ends when issue goes into “Resolved” state.
In the import that I have in eazyBI I have a series of properties regarding “SLA TT REALE” (see the img below):

Cattura

By selecting “SLA TT Reale issues” I have all the tickets open to the supplier.
If I select “SLA TT Reale - Breached” I have all the tickets that exceed SLA.
By selecting “SLA TT Reale Elapsed hours” I should have the sum of all the hours of all issues breached and met.
How can I only count the hours for issues that have breached?
Afterwards I have to convert this value to the days to calculate the penalties.
As you can see from the following table, the count is currently made for all issues:

I hope I have been clear and appreciate any help or advice you can give me.

Regards
Fra

Hi @Fra!

In the Penalty formula above there is the filtering option of

...
[Measures].[Time to resolution - Breached] > 0
...

This would calculate the Penalty only for the breached issues (in your case you would change the naming to your SLA: [Measures].[SLA: SLA TT REALE - Breached]).

Secondly, the Days over target calculation would only calculate how many days the breached issues are over the target so you can use this further in the Penalty calculation.

Please start with the target import and create the report as far as you can get based on the steps in the previous response (note your SLA field ID and name in all the formulas).
Let me know if you get stuck and in which of the steps!

Lauma / support@eazybi.com

1 Like

Hi @lauma.cirule
I created the custom field (SLA TARGET) through JavaScript but I get an error when I import the data, see img below:

Cattura

what should I do?

Thanks
Fra

Hi @Fra,

You can check and debug the JavaScript for a particular issues in Source data import screen. See more about JavaScript debugging here https://docs.eazybi.com/eazybijira/data-import/custom-fields/javascript-calculated-custom-fields#JavaScriptcalculatedcustomfields-ValidatetheJavascriptcodebeforeusingitinthecustomfielddefinition.

If you need assistance with it, could you please send me the JavaScript you used and what is the ID of the SLA custom field to support@eazybi.com (please mention this community request, so I can pick it up).

Lauma / support@eazybi.com

@Fra ,

I just wanted to let you know that my colleague did an improvement to the code above making sure it runs only on issues that are subject to this SLA. This might fix also the problem you had. I updated the code in the original comment above.

Lauma / support@eazybi.com

1 Like

Bonjour @lauma.cirule j’ai un problème un peut similaire maintenant je viens d’intaller Time to SLA et je veux faire le link avec eazyBI mais je suis un peut perdu au niveau de la configuration source Confluence Mobile - Snapbytes Confluence quand j’ai ajouter un champ personnaliser l’importation de mes données ne passent plus
Aider moi svp

Hi @Absa,
It seems you are having some technical issues with SLA import. In this case, please get in touch over e-mail with support@eazybi.com with more details of the steps you have taken and what error you get.

Lauma / support@eazybi.com

Salut @lauma.cirule oui j’ai d’enorme problème a ce niveau
d’accord je vais les contacter. Merci