Calculations

expression

Use this powerful function to compare attributes of fields. Compare system estimate and date fields, and any numeric, date, or datetime custom field.


expression(Subquery, expression)

expression examples

Due to the huge scope of this function, we will start with simple examples and expand.

Work logged and time tracking

As a project manager you want to filter issues based on the Time Spent and the Original Estimate fields.

For example, you want to see where more work was logged than originally estimated so you can identify if there was an issue with planning the scope of the issue:


issueFunction in expression("", "timespent > originalestimate")

This can also be achieved using plain JQL (workratio > 1) however with plain JQL you can not find issues which are likely to exceed their estimate. For example, as a project manager you want to see a list of all issues on track to exceed their work estimate so you can properly allocate resources:


issueFunction in expression("", "timespent + remainingestimate > originalestimate")

You can further narrow down the filter by adding a subquery. For example, to only show issues that are in progress, you can use the resolution is empty subquery, to filter out issues that have been completed:


issueFunction in expression("resolution = empty", "timespent + remainingestimate > originalestimate")

You may want to search for issues where the work logged exceeded the original estimate by more than a certain number of days or weeks. For example, any issues where the time spent exceeded the original estimate by more than 5 working days (normalised for timetracking, so > 40 hours work logged):


issueFunction in expression("", "timespent > originalestimate + 5*wd")

Ensure you use 5*d or 5*wd and not 5d as in dateCompare. The syntax is different.

Notes on time tracking

Unfortunately, a "day" can mean different things in different contexts. When comparing dates, we normally think in terms of the difference between two dates being 24 hour days.

However when comparing estimate fields, a day is normally thought of as an 8-hour working day, or whatever the Jira time tracking is configured to be. Similarly, a week usually consists of 5 working days.

If you want to compare estimates adjusted for time tracking, you need to explicitly specify either working day units:

wd - Number of milliseconds in a working day (according to your specification in Admin → Time tracking).
ww - Number of days in a working week multiplied by the wd value.

Or, if you are comparing time tracking fields with non-time tracking fields, for example remaining effort and due date, you need to use the fromTimeTracking function.

For example, as a project manager you want to search for issues which, based on their remaining estimate, are going to miss their due date, so you can devote extra resources to these to ensure that doesn’t happen:


issueFunction in expression("resolution is empty", "now() + fromTimeTracking(remainingestimate) > duedate")

When you specify an estimate of 3 days, Jira stores that internally as 24 hours (when wd is set to 8, wd*3), and renders it as 3 days for estimate fields. The fromTimeTracking function converts that to 72 hours (24*3) so it can be used to manipulate and compare with other dates.

Sum of multiple custom fields

Find issues where the product of two number custom fields is greater than X.

Custom field names are likely to have spaces, which can’t be parsed. If so, remove the spaces. It’s not case-sensitive but use camel-case for maximum readability. If your field names have any other punctuation you must use the format customfield_12345.

You can only use custom fields that have a configured searcher, as, for performance reasons, we only retrieve values from the Lucene index. The standard searchers are supported, searchers supplied by plugins will not work.

For example, as a project manager I want to find issues which have a certain ROI (return on investment) value so I can prioritise these in the next sprint. I want to show issues who's story points (max 3) divided by business value (max 100) is more than 50.


issueFunction in expression("", "StoryPoints / BusinessValue > 50")

I want to show all issues created by the team on behalf of a customer so I can prioritise these. To do that I can run the following to find issues where the creator is not equal to the reporter:


issueFunction in expression("", "creator != reporter")

I want to find issues that were due on the same day they were created so I can analyse why these were not identified in advance.


issueFunction in expression("", "created.clearTime() == dueDate")

Or find issues with a specific due date:


issueFunction in expression("", "dueDate == date('2018-05-10')")

Use date function when you need to compare date in expression. The function takes date as string and returns a timestamp. To get the desired result use clearTime with Jira fields such as created or updated to remove time if you wish to use == operator with date function.

Find issues that have a high ratio of votes to complexity (assuming Complexity is a numeric field):


issueFunction in expression("", "votes / Complexity > 100")

Conversions

Fields are passed to your expression with certain types, so you can do arithmetic and comparison:

Durations, such as time tracking, or where you have used the Duration searcher - Provided as a Long value of the number of milliseconds.
DatesA java.sql - Timestamp object. Where there is no time component such as with due date, the time portion will be set to midnight on that date. You can use the .clearTime() method to clear the time portion for == comparisons with other dates.
Users - A String containing the user key. This is sufficient as they are just used for equality comparisons.

In addition you can add a date field to a duration and get a new date.

Using functions

You can use all of the Date and User functions, for instance now(), startOfDay() etc, anywhere you would use a date.

Some arguments will need to be quoted. For example, if you want to say one week before the start of the month you would write startOfMonth('-1w').

aggregateExpression

Often you have a requirement to show some summary data based on the issues in the filter, for instance, you select all open issues in a version, and you want to see the total estimated time for all issues. Probably this should be called a summary function not an aggregate function, however this is a bit ambiguous in jira-land.

If you need more than simply a couple of values then you should probably consider writing a report. Most people will do these calculations in Excel anyway, but an aggregate function can draw attention to some figure, eg total remaining estimate from all issues shown in the current query.

The aggregate function is added to the JQL because in doing that, it will ensure other people who run the same query also see the summary value(s). Note that these won’t appear in excel views, or anywhere other than the issue navigator. Adding an aggregate function does not change the results from the query in any way.

For example, to see the total estimate for all issues in the LOAD project run this JQL:


project = LOAD and issueFunction in aggregateExpression("Total Estimate for all Issues", "originalEstimate.sum()")

If the function has just one argument, the data label will be Aggregate data value. The expression can have multiple values, in which case use: (label1, expr1, label2, expr2, …).


project = LOAD and issueFunction in
    aggregateExpression("Total Estimate for all Issues", "originalEstimate.sum()", "Remaining work", "remainingEstimate.sum()")

results in:

Some other examples for summary data:

Note	Argument
Total time spent on these issues	timespent.sum()
Average original estimate of these issues	originalestimate.average()
Average work ratio	workratio.average() Note: Will display as a decimal (e.g. 0.12 rather than 12%)
Total remaining work	remainingEstimate.sum()
Tracking error	(originalEstimate.sum() - timeSpent.sum()) / remainingEstimate.sum()
Number of issues in this list created by user jbloggs	reporter.count('jbloggs')
Simple breakdown of reporter but you’re probably better off using a pie chart as this is not displayed nicely at the moment	reporter.countBy{it}