Calculate sum of values

[Robi Anton]

Hellow, 

I want to calculate the sum of originalEstimate time (system field) && Real time (customField). 
Then, I want to print these results on custom fileds (then, I can print them on gadgets....).
With conditions: only tickets from previous month (updatedDate >= startOfMOnth(+1) and updatedDate <= endOfMonth(-1)) and status = Termindated)

I have multiple possibilities:
Plugins, but I don't want to pay.
Formula on scripted field: but I don't know how to create the formula for my demand. Is there a tutorial?
Script on scripted field or groovy script: how to create the script?  Tutorial?

My idea for the script, if someone could do it, it will be nice

for ( status = Terminated && (updatedDate >= startOfMonth(-1) AND updateDate <= endOfMonth(-1)))

 {

//for each ticket

sum+= sum of the value "originaleEstimate"

}

[Kristian Walker _Adaptavist_ · Accepted solution]

Hi Robi,

Thanks for Clarifying your requirements.

Looking into this there is already a built in JQL function ins Script Runner called aggregateExpression. This can be used in a filter similar to the JQL below which will return the rolled up results shown in the screenshot below. Note - You will need to change the JQL query before the aggregrateExpression() method to the query you need to pull in the correct issues that are to be summed up.

project = "Kristians Dev Project" AND "Start Date" >= startOfMonth() AND "End Date" <= endOfMonth() AND issueFunction in aggregateExpression("Total Estimate for All Issues", "originalEstimate.sum()", "Remaining work", "remainingEstimate.sum()", "Time Spent","timeSpent.sum()")

image2016-3-8 10:10:4.png

This filter can then be replace in a filter results gadget on a dashboard showing the Original Estimate, Remaining Estimate and Time Spent sum fields as shown below.

image2016-3-8 10:19:54.png

Please note currently SR does not provided scripted dashboard gadgets and the aggregated results box will not show on the dashboard in the filter results gagdet as this will show the sum results for each issue but users can click through to the filter to see the aggregrated results shown above.

If you wanted to show the filter page above then you could look to enable the text gadget in JIRA as described here and add the filter URL to an iframe as workaround.

I hope this helps

Thanks

Kristian

[Robi Anton]

Thanks Kristian, 

But the Text Plugin is vulnarable to XSS attacks.... So I'm waiting to the order of my manager. 

Instead, my first idea was to do the same thing on a scripted field. Do you think it's a good idea?

Or on a groovy script. in this case, I must use other request, because the aggregate functions don't store result in any fields.  

[Kristian Walker _Adaptavist_]

A scripted field would be summing the values and storing it on each issue. This means the values would get re calculated and the script fired each time an issue returned in the query is loaded. This would place extra load on the server when the value can be calculated when a single filter is run.

If this is just for reporting purposes then I would simply stick to the approach above and have users click through to run the filter when they wish to see the results.

One alternative may be the Custom Content for JIRA plugin to place the filter as a link on the dashboard.

Kristian

[Robi Anton]

Your are right. 

I think I will call a post-function and execute a groovy script on a specific condition or a new project for calculate the stat....

But I need help for the script:

for ( Project = "XXX" and  status = Terminated && (updatedDate >= startOfMonth(-1) AND updateDate <= endOfMonth(-1)))

 {

//for each issue

sum+= sum of the value "originaleEstimate"

}

I have tested the Custom Content plugin, but I dont like it. It show me the direct web page. 

I want the result on a board like in BartChart... 

[Kristian Walker _Adaptavist_]

Hi Robi,

Your requirements above are a bit unclear.

I assume what you would like to do is add a field on each issue which takes the value of the Original estimate field in milliseconds and the value of a Date/Time field in milliseconds and adds these together and stores the result on the issue.

This can be achieved using a Script Field provided by the Script Runner plugin. Once you have this value from a Scripted Field on each issue then you can place display the field in a filter results gadget on a dashboard. 

I have pasted below some  example code and config for a Script Field which will sum together the Original estimate value and current time value in milliseconds and display this on an issue.

import com.atlassian.jira.issue.*;
import com.atlassian.jira.component.ComponentAccessor
import java.sql.Timestamp

// Get the current issue key
Issue issueKey  = issue
def id=issueKey.getId()

// Get access to the Custom Field Manager
def customFieldManager = ComponentAccessor.getCustomFieldManager();
// Get the required custom field values
def realTime = getCustomFieldValue("Start Date") as Timestamp
def originalEstimate = issue.getOriginalEstimate()

// Get the time of the Date field in milliseconds
def realtimeval = realTime.time

// Check if both fields contain values
if (realTime && originalEstimate){
    // Sum together the values

    def sumValue =  originalEstimate + realtimeval
   // Display the result on the issue
    return realtimeval
}

image2016-3-7 15:51:26.png

I hope this helps you to get started to configure a script field to forfill your requirements.

Kristian

[Robi Anton]

Calling by a scheduler....

import org.apache.log4j.Category
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.search.SearchException
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.jira.issue.util.IssueChangeHolder
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder;
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.ComponentManager;
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.issue.customfields.manager.OptionsManager
import com.atlassian.jira.issue.customfields.option.Option
import com.atlassian.jira.issue.*;
import java.sql.Timestamp
Calendar localCalendar = Calendar.getInstance(TimeZone.getDefault());
int currentDay = localCalendar.get(Calendar.DATE);
println currentDay

if ( currentDay == 18 ){
println "WE ARE IN CURRENT DAY _script Somme Precedent"
def jqlSearch = "project = 'AISC - Suivi des demandes' and status was in (Validation) and ('Date de livraison' >= startOfMonth(-1) and 'Date de livraison' <=endOfMonth(-1))"
def userManager = ComponentAccessor.getUserManager()
def issueManager = ComponentAccessor.getIssueManager()
def searchService = ComponentAccessor.getComponentOfType(SearchService)
def user = userManager.getUserByName("yk3520").directoryUser
SearchService.ParseResult parseResult = searchService.parseQuery(user, jqlSearch)

def value = 0
def customFieldManager = ComponentAccessor.getCustomFieldManager()
def myCustomField = customFieldManager.getCustomFieldObjectByName("Temps d'intervention")

if (parseResult.isValid()) {
try {
def results = searchService.search(user, parseResult.getQuery(), PagerFilter.getUnlimitedFilter())
def issues = results.getIssues()
issues.each {
if (it.getCustomFieldValue(myCustomField))
value += it.getCustomFieldValue(myCustomField).toInteger()
}
} catch (SearchException e) {
e.printStackTrace()
}
} else {
log.warn("Invalid query")
return null
}

// Save the value at custom field with name A number custom field of issue with key TEST-1
def targetIssue = issueManager.getIssueByCurrentKey("SL-8")
def customField = customFieldManager.getCustomFieldObjects(targetIssue).find {it.name == "Total intervention M-1"}
if (customField) {
    def changeHolder = new DefaultIssueChangeHolder()
    customField.updateValue(null, targetIssue,
            new ModifiedValue(targetIssue.getCustomFieldValue(customField), value.toString()),changeHolder)
    log.debug "Custom field $customField.name} updated with value ${value}"
    return
}
log.warn "Custom field ${customField?.name} was not updated"

}
else
{
println "WE ARE NOT IN CURRENT DAY"
}

[Ratna Kumar Lekkala]

Hi,

how can I check for the originalEstimate.sum() to be greater than 0 for an issue to be moved into in progress?

[JamieA]

This seems like a very different question - worth asking a new question here.

[Robi Anton]

Hello Kristian, 

I'll explain my question more clearly. 

For example, I have 20 tickets for the current month, and 30 tickets for the previous month. 

On each issue, I have these informations: OriginalEstimateTime, RemainingTime, SpentTime.

For all the tickets, I want to add 2 more fields: "SumOriginalEstimatePreviousMonth" and "SumSpentTimePreviousMonth". 

SumOriginalEstimatePreviousMonth : sum of OriginalEstimateTime of previous month (30 issues)

SumSpentTimePreviousMonth: sum of SpentTime of previous month (30 issues)

So in all the issues (20 + 30), we can have the sum of estimated and spent time for the previous month. 

My main idea, is to hide these fields from all the issues, and use them on a gadget of the dashboard. 

[Robi Anton]

We have script runner right. 

[JamieA]

Which plugin are you using? Do you have ScriptRunner already? You have tagged two plugins here, which work differently.