Calculate the WorkRatio for the sum of all Subtasks

[Alexander Pappert]

Hi,

we have a Project with Tasks and Subtasks. The time logging is only done in the Subtasks. The WorkRatio is automatically calculated by JIRA for the Subtasks.

But it is not working for the Tasks (with the sums: E-original Estimate).
So i used the addon "JIRA Misc Custom Fields" with a "Calculated Number Field".

My sum calculation works, but I found that it creates a lot of Errors in the logfile.

Calculation:

<!-- @@Formula: (issue.get("aggregatetimespent") != null ? issue.get("aggregatetimespent") : 0) *100 / (issue.get("aggregatetimeoriginalestimate") != null ? issue.get("aggregatetimeoriginalestimate") : 0) -->

I used the "(issue.get("customfield_10114") != null ? issue.get("customfield_10114") : 0)" from https://innovalog.atlassian.net/wiki/spaces/JMCF/overview#JIRAMiscCustomFields-calculatednumberfield
I am not sure, but I think it sorts out empty or 0 values.

Error in logfiles:
Target exception: java.lang.ArithmeticException    
Navigate to the following URL to edit the formula: http://...:8080/secure/admin/EditCustomField!default.jspa?id=11107
2017-10-11 09:28:48,628 http-nio-8080-exec-13 ERROR zeppbot 568x194632x1 mufley 10.154.3.62 /rest/cb-automation/latest/project/GLOBAL/rule/29/execute [c.i.jmcf.fields.CalculatedNumberField] CalculatedNumberField: error evaluating formula of field "KPI" of issue TEST-309:
    Sourced file: inline evaluation of: `` (issue.get("aggregatetimespent") != null ? issue.get("aggregatetimespent") : 0) . . . '' : Arithemetic Exception in binary op : at Line: 1 : in file: inline evaluation of: `` (issue.get("aggregatetimespent") != null ? issue.get("aggregatetimespent") : 0) . . . '' : ;

Is there soms built-in JIRA field for this one that I can use instead of the calculated field?
What is wrong in my Formula?

[Nic Brough -Adaptavist-]

Could you explain "The WorkRatio is automatically calculated by JIRA for the Subtasks.  But it is not working for the Tasks" more?

[Alexander Pappert]

I mean the red marked JIRA Field "WorkRatio".
But workratio is only filled for the Subtasks.

Thats why i tried to calculate it manually as KPI, see the green marking.

[Nic Brough -Adaptavist-]

There is no work ratio for the top-level issue, you already said you don't put estimates on it.

I'm not sure how you'd get the data that sums up the ratios for the sub-tasks on an issue in JMCF

[Alexander Pappert]

The sum up data are the jira fields:
aggregatetimespent
aggregatetimeoriginalestimate
They are calculated by jira.

The jira workratio is calculated with timespent and originalesimate, not with the "aggreagte" ones.


I edited my script a bit because  it only set null to 0. I forgot that I also had some 0 as values, which I must exclude because Division by 0 is not allowed.
I think that these were my Errors in the logfile.

It works for me now.

[Liat Yanco]

Hi @Alexander Pappert ,

I know it is from a long time ago, but any chance you can share the script after your correction?

Thanks,

Liat.

[Alexander Pappert]

@Liat Yanco 

there it is

//KPI -- %
<!-- @@Formula:

if (issue.get("aggregatetimespent") == 0) // time spent=0h, also %=0
return 0;

else if (issue.get("aggregatetimeoriginalestimate") == 0) // original estimate 0h
return (issue.get("aggregatetimespent") != null ? 999 : 0); // bei time spent LEER wird 0 ausgegeben, sonst 999

else if (issue.get("aggregatetimeoriginalestimate") == null) // original estimate ist LEER
return (issue.get("aggregatetimespent") != null ? 999 : 0); // bei time spent LEER wird 0 ausgegeben, sonst 999

(issue.get("aggregatetimespent") != null ? issue.get("aggregatetimespent") : 0) *100 / (issue.get("aggregatetimeoriginalestimate") != null ? issue.get("aggregatetimeoriginalestimate") : 0)
// KPI = time spent *100 / original estimate
// das *100 muss zuerst kommen, das es Ganzzahlen sind

also with color formatting

<!-- @@Format:
if (value == 999)
return "<span style=color:#0000FF>" + numberTool.format("0", value) + "</span>"; //FF0000 = blue 
else if (value == 0)
return numberTool.format("0", value);
else if (value < 90)
return "<b><span style=color:#009933>" + numberTool.format("0", value) + "</span></b>"; //FF0000 = green
else if (value > 110)
return "<b><span style=color:#FF0000>" + numberTool.format("0", value) + "</span></b>"; //FF0000 = red
else if (value > 100)
return "<b><span style=color:#FF9900>" + numberTool.format("0", value) + "</span></b>"; //FF0000 = orange
else if (value > 95)
return "<b>" + numberTool.format("0", value) + "</b>";
numberTool.format("0", value)
-->

[Liat Yanco]

thanks a lot! it was really helpful :)