The formula fields library provides you with an overview of all available templates and additional use cases (example expressions) that should give you an easy start on how to use Advanced Formula Fields.
Make sure to come by once in a while since the number of use cases will grow over time.
Templates
Advanced Formula Fields provides a varied set of preconfigured Templates you can use with minimal configuration. These are shown if you Create and configure formula fields or when you Manage existing formula fields / Field overview.
They are group into logical categories depending on the use case or the data that you likely want to display. These categories are:
📚 Additional use cases using custom formulas
In addition to the built-in templates we have collected a large collection of uses cases to be used in Custom number formula and Custom text formulafields.
🔢 Custom number formulas
| Use case | Parsing mode | Expression |
|---|---|---|
| Average time since creation of sub-tasks |
General |
%{floor(avg(toNumberList(jiraExpression("issue.subtasks.map(i=>(Number(new Date())-
Number(i.created)))"))))}
|
| Completion percentage by status |
General |
%{%{issue.status} = "Open" ? 0 :
( %{issue.status} = "Planning" ? 15 :
( %{issue.status} = "In Progress" ? 60 :
( %{issue.status} = "In Review" ? 70 :
( %{issue.status} = "Implementing" ? 85 : 100 ))))}
|
| Count frequency of a keyword in social media content |
General |
%{count(findPattern(%{issue.cfnnnnn}, %{issue.cfmmmmm}))}
|
| Count labels |
General |
%{issue.labels.length}
|
| Days until due date |
General |
%{{issue.dueDate} != null ? floor(max({issue.dueDate} -
datePart({system.currentDateTime}, RUN_AS_LOCAL), 0) /
DAY) : null
|
| Difference between the current day and the earliest sub-task due date |
General |
%{max(fieldValue({issue.dueDate},subtasks())) != null ? ceil((
min(filterByValue(fieldValue({issue.dueDate},subtasks()),!=,
null )) - {system.currentDateTime}) / DAY) : null}
|
| Elapsed time between creation and resolution |
General |
%{{issue.resolutionDate} != null ? (({issue.resolutionDate} - {issue.created}))
: null}
|
| Highest value out of several fields |
General |
%{max([{issue.cfAAAAA}, {issue.cfBBBBB}, {issue.cfCCCCC},
{issue.cfDDDDD}, {issue.cfEEEEE}])}
|
| Multiply and sum field values from sub-tasks |
Jira expression |
issue.subtasks.length && issue.subtasks.map( s => (s.customfield_nnnnn || 0) * (s.customfield_ppppp || 0)) .reduce((a, b) => a + b) |
| Number of characters in a field |
General |
%{count(findPattern(%{issue.cfnnnn}, "."))}
|
| Number of sub-tasks based on status |
General |
%{count(filterByStatus(subtasks(), "Done, Completed"))}
|
| Number of sub-tasks based on status and work item type |
General |
%{count(filterByIssueType(filterByStatus(subtasks(), "Done,
Completed"), "Sub-task, Sub-story"))}
|
| Number of times that a custom field has been changed |
Jira expression |
issue.changelogs .filter(changelog => changelog.items .some(item => item.fieldId == 'customfield_nnnnn')) .length |
| Number of work items with the same fix versions |
General |
%{%{issue.fixVersions} != null ? count(issuesFromJQL(
"fixVersion in ('" + jiraExpression( "issue?.fixVersions?.map(
v => v?.name).join(\"','\") ") + "') ")) : null}
|
| Priority assessment based on field values |
General |
%{sum([%{issue.cfnnnnn} = "Option 1" ? 1 : 0, %{issue.cfppppp}
= "Option 2" ? 1 : 0 ])}
|
| Remaining budget - expenses tracked in sub-tasks |
General |
%{{issue.cfnnnnn} = null ? null : {issue.cfnnnnn} - sum(
append(fieldValue({issue.cfppppp}, subtasks()), [0]))}
|
| Sum completed sibling items’ story points |
General |
%{sum(fieldValue({issue.cfnnnnn},issuesFromJQL("statusCategory = Done AND 'parent' = "+%{parent.key})))}
|
| Sum of field values from sub-tasks |
General |
%{sum(fieldValue({issue.cfnnnn}, subtasks()))}
|
| Sum of field values from work items in JQL query |
General |
%{sum(fieldValue({issue.cfnnnnn},issuesFromJQL("project = DEMO
and 'fieldName' is not empty")))}
|
| Sum of time spent on blocking work items |
General |
%{sum(append(fieldValue({issue.timeSpent},linkedIssues("is
blocked by")), [0]))/60}
|
| Sum of time spent on epic and child work items |
General |
%{%{issue.issueType} = "Epic" ? (sum(append(fieldValue(
{issue.timeSpent}, issuesUnderEpic()), [{issue.timeSpent}] ))
/ 60) : null}
|
| Sum of time spent on work items with same fix version |
General |
%{%{issue.fixVersions} != null ?
sum(append(fieldValue({issue.timeSpent},
issuesFromJQL("fixVersion = '"+%{issue.fixVersions.id}+"'")),[0]))/60)
: null}
|
| Time left until due date |
General |
%{{issue.dueDate} != null ? max({issue.dueDate} -
{system.currentDateTime}, 0) / DAY : null}
|
| Time since creation |
General |
%{{system.currentDateTime} - {issue.created}}
|
| Total original estimate of blocking work items |
General |
%{sum(append(fieldValue({issue.originalEstimate},linkedIssues(
"is blocked by")),[0]))/60}
|
| Total remaining estimate of blocking work items |
General |
%{sum(append(fieldValue({issue.remainingEstimate},linkedIssues(
"is blocked by")),[0]))/60}
|
🔡 Custom text formulas
| Use case | Parsing mode | Expression |
|---|---|---|
| Components from all sub-tasks |
General |
%{distinct(fieldValue(%{issue.components}, subtasks()))}
|
| Earliest date and time from linked work items |
Jira expression |
let minDate = issue?.links.map(link => link.linkedIssue.customfield_nnnnn).filter( d => d != null); minDate.length > 0 ? new Date(minDate.reduce((a, b) => a < b ? a : b )).toString() : "" |
| Earliest date from linked work items |
Jira expression |
let minDate = issue?.links.map(link => link.linkedIssue.customfield_nnnnn).filter( d => d != null); minDate.length > 0 ? new CalendarDate(minDate.reduce((a, b) => a < b ? a : b )).toString() : "" |
| Field value validation |
General |
%{matches(%{issue.cfnnnnn},"6|7|8|9") ? "Valid":"Invalid"}
|
| First comment |
Jira Expression |
issue?.comments[0]?.body?.plainText == null ? '' : issue?.comments[0]?.body?.plainText |
| Historical due dates |
Jira expression |
issue.changelogs.filter(changelog => changelog.items.some(item
=> item.fieldId == 'duedate' && item.from != null)).map(
changelog => {duedateentry: changelog.items.filter(item =>
item.fieldId == 'duedate' )}).map(e => new CalendarDate(
e.duedateentry[0].from).toString()).join(", ")
|
| Keys of linked work items in current project |
General |
%{filterByProject(linkedIssues(), %{issue.project.key})}
|
| Keys of non-estimated sub-tasks |
Jira expression |
issue.subtasks.filter(s => s?.originalEstimate == null &&
s?.customfield_nnnnn == null).map(i => i.key).join(", ")
|
| Last status change date |
Jira expression |
issue.changelogs.filter(changelog => changelog.items.some(item => item.fieldId == 'status')).map(changelog => changelog.created).concat(issue.created)[0].toString() |
| Previous reporter |
Jira expression |
let reporters= issue.changelogs.map(i=>i.items).flatten().filter(i=>i.field=="reporter");
reporters.length>0 ?
reporters.map(a=>a.fromString)[0] == null ?
""
:reporters.map(a=>a.fromString)[0]
: ""
|
| Project category |
General |
%{issue.project.category}
|
| Project lead |
General |
%{issue.project.leadDisplayName}
|
| RAG - due date traffic light |
General |
%{{issue.dueDate} = null or %{issue.resolution} != null? "⚪" :
(addDays({system.currentDateTime}, 14, RUN_AS_LOCAL) < {issue.dueDate} ? "🟢" :
(addDays({system.currentDateTime}, 7, RUN_AS_LOCAL) < {issue.dueDate} ? "🟡" :
({system.currentDateTime} < {issue.dueDate} ? "🟠" : "🔴")))}
|
| RAG - status traffic light |
General |
%{%{issue.status.category} = "To Do" ? "🔴⚪️⚪️" :
( %{issue.status.category} = "In Progress" ? "⚪️🟡⚪️" :
( %{issue.status.category} = "Done" ? "⚪️⚪️🟢" :
"⚪️⚪️⚪️" ))}
|
| Sub-task assignees |
General |
%{distinct(fieldValue(%{issue.assignee.displayName}, subtasks()))}
|
| Sub-task labels |
General |
%{distinct(fieldValue(%{issue.labels}, subtasks()))}
|
| Sub-task reporters |
General |
%{distinct(fieldValue(%{issue.reporter.displayName}, subtasks()))}
|
| Validate URLs for Google Analytics |
General |
%{count(findPattern(%{issue.cfnnnnn},
"\b(?:https?://|www\.)\S*(?=.*utm_source=)(?=.*utm_medium=)(?=.*utm_campaign=)\S\b"))
= count(findPattern(%{issue.cfnnnnn}, "\b(?:https?://|www\.)\S+\b")) ?
"🟢" : "🔴"}
|
| Visual progress of sub-tasks |
General |
let subtasks = issue.subtasks|| [];
if (subtasks.length == 0){ 'No sub-tasks' }
else{ let doneSubtasks = subtasks.filter(s => s.status.name ==
"Done");
let value = (doneSubtasks.length / subtasks.length) * 100;
let percent = value - value % 1 + (value % 1 >= 0.5 ? 1 : 0);
let totalBlocks = 10;
let filled = (percent / 100) * totalBlocks;
let full = filled - filled % 1 + (filled % 1 >= 0.5 ? 1 : 0);
let bar = '🟩'.repeat(full) + '⬜️'.repeat(totalBlocks - full);
`${bar} ${percent}%` }
|
| Visual progress of work item |
General |
%{%{issue.status} = "Open" ? "🟩🟩🟩🟩🟩" :
( %{issue.status} = "Planning" ? "🟩🟩⬜️⬜️⬜️" :
( %{issue.status} = "In Progress" ? "🟩🟩🟩⬜️⬜️" :
( %{issue.status} = "In Review" ? "🟩🟩🟩🟩⬜️" :
( %{issue.status} = "Done" ? "🟩🟩🟩🟩🟩" :
"⬜️⬜️⬜️⬜️⬜️" ))))}
|
| Work item ID |
General |
%{issue.id}
|