The Structure plugin for Jira is very useful for everyday work with tasks and their analysis; it takes the visualization and structuring of Jira tickets to a new level, and does this all right out of the box.
And, not everyone knows it, but the functionality of Structure formulas can simply blow your mind. Using formulas, you can create extremely useful tables that can greatly simplify work with tasks, and most importantly, they are useful to perform a deeper analysis of releases, epics, and projects.
In this article, you’ll see how to create your own formulas, starting from the simplest examples and ending with complex, but rather useful, cases.
So, who is this text for? One might wonder, why write an article when the official documentation on the ALM Works website is right there waiting for readers to dig in. That’s true. However, I’m one of those people who didn’t even have the slightest notion that Structure was hiding such wide functionality: “Wait, this was an option all along?!” That realization got me thinking, there might be other people who also still don’t know the kind of things they can do with formulas and Structure.
This article will also be useful for those who are already familiar with formulas. You’ll learn some interesting practical options for using custom fields and, perhaps, borrow some of them for your projects.By the way, if you have any interesting examples of your own, I’ll be glad if you share them in the comments.
Each example is analyzed in detail, from the description of the problem to the explanation of the code, thoroughly enough so that there will be no questions left. Of course, alongside the explanations, each example is illustrated by code that you can try out for yourself without delving into the analysis.
If you don’t feel like reading, but you’re interested in formulas, check out the ALM Works webinars. These explain the basics in 40 minutes; the information is presented there in a very compressed manner.
You don’t need any additional knowledge to understand the examples, so anyone who has worked with Jira and Structure will be able to repeat the examples in their tables without any problems.
The developers provided a fairly flexible syntax with their Expr language. Basically, the philosophy here is “write as you want, and it’ll work”.
So, let’s get started!
Why do we need formulas?
So, why would we want to use formulas at all? Well, sometimes it turns out that we don’t have enough standard Jira fields, such as “Assignee”, “Story Points”, and so on. Or we need to calculate some amount for certain fields, display the remaining capacity by version, and find out how many times the task has changed its status. Maybe we even want to merge several fields into one in order to make our Structure easier to read.
To solve these problems, we need formulas, and we’ll use those to create custom fields.
The first thing we need to do is to understand how a formula works. It allows us to apply some kind of operation to a string. Because we’re uploading many tasks into the structure, the formula gets applied to each line of the entire table. Usually, all its operations are aimed at working with tasks in these lines.
So, if we ask the formula to display some Jira field, for example, “Assignee”, then the formula will be applied for each task, and we’ll have another “Assignee” column.
Formulas consist of several basic entities:
- Variables — for accessing Jira fields and saving intermediate results
- Built-in functions — these perform a predefined operation, for example, count the number of hours between dates or filter data in an array
- Custom functions — if we need unique calculations
- Different forms of displaying the result, for example, “Date/Time”, “Duration”, “Number” or “Wiki Markup” for your option.
Getting to know formulas
We’ll become more familiar with formulas and their syntax via some examples, and we’re going to go through six practical cases.
Before looking at each example, we’ll indicate which Structure features we’re using; new features that haven’t yet been explained will be in bold. Each of the following examples will have an increasing level of complexity. They are arranged in order to gradually introduce you to the important formula features.
Here’s the basic structure you’ll see each time:
- The problem
- The proposed solution
- The Structure features used
- A code example
- An analysis of the solution
These examples cover topics ranging from variable mapping to complex arrays:
- Two examples displaying the start and end dates of work on a task (options with different display)
- A parent task — displaying the type and name of the parent task
- The sum of Story Points of subtasks and the status of these assessments
- An indication of recent changes in task status
- A calculation of working time, excluding days off (weekends) and extra statuses
Creating formulas
First, let’s figure out how to create custom fields with formulas. In the upper-right part of Structure, at the end of all columns, there is a “+” icon — click on it. In the field that appears, write “Formula …” and select the appropriate item.
Saving formulas
Let’s discuss saving a formula. Unfortunately, it’s still not possible to save a specific formula separately somewhere (only in your notebook, as I do). At the ALM Works webinar, the team mentioned that they’re working on a bank of formulas, but for now the only way to save them is to save the entire view along with the formula.
When we’re done working on a formula, we need to click on our structure’s view (it will most likely be marked with a blue asterisk) and click “Save” to overwrite the current view. Or you can click on “Save As…” to create a new view. (Don’t forget to make it available to other Jira users as new views are private by default.)
The formula will be saved to the rest of the fields in a particular view, and you can see it in the “Advanced” tab of the “View Details” menu.
Starting with version 8.2, Structure now has the ability to save formulas in 3 quick clicks.
The save dialog is available from the formula editing window. If this window is not open, just click on the triangle ▼ icon in the desired column.
In the edit window we see the “Saved Column” field, to the right there’s an icon with a blue notification, which means that the changes in the formula have not been saved. Click on this icon and select the “Save as…” option.
Then enter names for our column (formula) and choose in which space to save it. “My Columns” if we want to save it in a personal list. “Global”, so that the formula will be saved in the general list, where it can be edited by all users of your Structure. Click “Save”.
Now our formula is saved. We can load it in any structure or resave it from anywhere. By resaving the formula, it will be updated in all structures in which it is used.
Variable mapping is also saved with the formula, but we’ll talk about mapping later.
Now, let’s move on to our examples!
Displaying the start and end dates of work on a task
Problem
We need a table with a list of tasks, as well as the start and end dates for working on those tasks. We also need the table to export it to a separate excel-gantt. Unfortunately, Jira and Structure don’t know how to provide such dates out of the box.
Proposed solution
The start and end dates are the dates of transition to specific statuses, in our case these are “In Progress” and “Closed”. We need to take these dates, and display each of them in a separate field (this is necessary for further export to gantt). So, we’ll have two fields (two formulas).
The Structure features used
- Variable mapping
- The ability to adjust display format
A code example
Field for the start date:
firstTransitionToStart
Field for the end date:
latestTransitionToDone
An analysis of the solution
In this case, the code is a single variable, firstTransitionToStart, for the start date field, and latestTransitionToDone for the second field.
Let’s focus on the start date field for now. Our goal is to get the date the task transitioned to the “In Progress” status (this corresponds to the logical start of the task), so the variable is named, quite explicitly to prevent the need for later guessing, as “first transition to start”.
To make a date into a variable, we turn to variable mapping. Let’s save our formula by clicking on the “Save” button.
Our variable appeared in the “Variables” section, with an exclamation mark next to it. Structure indicates that it can’t link a variable to a field in Jira, and we’ll have to do it ourselves (i.e. map it).
Click on the variable and go to the mapping interface. Select the field or needed operation — look for the operation “Transition Date …”. To do that, write “transition” in the selection field. You’ll be offered several options at once, and one of them suits us: “First Transition to In Progress”. But in order to demonstrate how the mapping works, let’s choose the “Transition Date …” option.
After that, you need to choose the status in which the transition occurred, and the order of this transition — the first or the last.
Select or enter in “Status” — “Status: In Progress” (or the corresponding status in your Workflow), and in “Transition” — “First transition to status”, since the beginning of work on a task is the very first transition to the corresponding status.
If instead of “Transition Date…” we chose the initially proposed option “First Transition to In Progress”, then the result would be almost the same — Structure would choose the necessary parameters for us. The only thing is, instead of “Status: In Progress”, we would have “Category: In Progress”.
Let me note an important feature: a status and a category are two different things. A status is a specific status, it’s unambiguous, but a category can include several statuses. There are only three categories: “To Do”, “In Progress” and “Done”. In Jira, they are usually marked with gray, blue, and green colors respectively. The status must belong to one of these categories.
I recommend indicating a specific status in cases like this in order to avoid confusion with statuses of the same category. For example, we have two statuses of the “To Do” category on the project, “Open” and “QA Queue”.
Let’s go back to our example.
Once we have selected the necessary options, we can click on “< Back to Variables List” to complete the mapping options for the firstTransitionToStart variable. If we do everything right, we’ll see a green check mark.
At the same time, in our custom field, we see some strange numbers that don’t look like a date at all. In our case, the result of the formula will be the value of the firstTransitionToStart variable, and its value is milliseconds since January 1970. In order to get the correct date, we need to choose a specific formula display format.
The format selection is located at the very bottom of the editing window. “General” is selected there by default. We need “Date / Time” to display the date correctly.
For the second field, latestTransitionToDone, we’ll do the same. The only difference is when mapping we can already select the “Done” category, and not the status (since there is usually only one unambiguous task completion status). We select “Latest Transition” as the transition parameter, since we are interested in the most recent transition to the “Done” category.
The final result for the two fields will look like this.
Now let’s see how to achieve the same result, but with our own display format.
Date display with our own format
Problem
We aren’t satisfied with the date display format from the previous example, since we need a special one for the gantt table — “01.01.2022”.
Proposed Solution
Let’s display the dates using the functions built into Structure, specifying the format that suits us.
Structure features used
- Variable mapping
- Expr functions
A code example
FORMAT_DATETIME(firstTransitionToStart;"dd.MM.yyyy")
An analysis of the solution
The developers have provided many different functions, including a separate one for displaying the date in our own format: FORMAT_DATETIME; that’s what we’re going to use. The function uses two arguments: a date and a string of the desired format.
We set up the firstTransitionToStart variable (first argument) using the same mapping rules as in the previous example. The second argument is a string specifying the format, and we define it like this: “dd.MM.yyyy”. This corresponds to the form we want, “01.01.2022”.
Thus, our formula will immediately give a result in the desired form. So, we can keep the “General” option in the field settings.
The second field with the end date of the work is done in the same way. As a result, the structure should look like in the image below.
In principle, there are no significant difficulties working with formula syntax. If you need a variable, write its name; if you need a function, again, just write its name and pass the arguments (if they are required).
When Structure encounters an unknown name, it assumes it’s a variable and tries to map it itself, or asks us for help.
By the way, an important note: Structure is case-insensitive, so firstTransitionToStart, firsttransitiontostart, and firSttrAnsItiontOStarT are the same variable. The same rule applies to functions. In order to achieve unambiguous code style, in the examples we will try to adhere to the rules of Capitalization Conventions by MSDN.
Now let’s delve into the syntax and look at a special format for displaying the result.
Displaying the name of the parent task
Problem
We work with regular tasks (Task, Bug, etc.) and with Story-type tasks that have subtasks. At some point, we need to find out what tasks and subtasks the employee worked on during a certain period.
The problem is that many subtasks don’t provide information about the story itself, as they are called “working on the story”, “setting up” or, for example, “activating the effect”. And if we request a list of tasks for a certain period, we’ll get a dozen tasks with the name “working on the story” without any other useful information.
We would like to have a view with a list divided into two columns: a task and a parent task, so that in the future it would be possible to group such a list by employees.
Proposed solution
On our project, we have two options when a task can have a parent:
- A task is a subtask and its parent is only Story
- A task is a regular task (Task, Bug, etc.) and it may or may not have Epic, in which case the task has no parent at all.
So, we must:
- Find out if a task has a parent
- Find out the type of this parent
- Work out the type and name of this task according to the following scheme: “[Parent-type] Parent-name”.
To simplify the perception of information, we will color the text of the task type: that is, either “[Story]” or “[Epic]”.
What we’ll use:
- Variable mapping
- Condition
- Access to task fields
- Display format — wiki markup
A code example
if(
Parent.Issuetype = "Story";
"""{color:green}[${Parent.Issuetype}]{color} ${Parent.Summary}""";
EpicLink;
"""{color:#713A82}[${EpicLink.Issuetype}]{color} ${EpicLink.EpicName}"""
)
An analysis of the solution
Why does the formula start with an if condition, if we just need to output a string and insert the task type and name there? Isn’t there some universal way to access task fields? Yes, but for tasks and epics, these fields are named differently and you also need to access them differently, this is a feature of Jira.
The differences begin at the level of the parent search. For a subtask, the parent lives in the “Parent Issue” Jira field, and for a regular task, the epic will be the parent, located in the “Epic Link” field. Accordingly, we’ll have to write two different options for accessing these fields.
This is where we need an if condition. The Expr language has different ways of dealing with conditions. The choice between them is a matter of taste.
There’s an “excel-like” method:
if (condition1; result1; condition2; result2 … )
Or a more “code-like” method:
if condition1 : result1
else if condition2 : result2
else result3
In the example, I used the first option; now let’s look at our code in a simplified way:
if(
Parent.Issuetype = "Story";
Some kind of result 1;
EpicLink;
Some kind of result 2
)
We see two obvious conditions:
- Parent.Issuetype = “Story”
- EpicLink
Let’s figure out what they do, and start with the first one, Parent.Issuetype=”Story”.
In this case, Parent is a variable that is automatically mapped to the “Parent Issue” field. This is where, as we discussed above, the parent for the subtask should live. Using dot notation (.), we access the property of this parent, in particular, the Issuetype property, which corresponds to the “Issue Type” Jira field. It turns out that the entire Parent.Issuetype line returns us the type of the parent task, if such a task exists.
Additionally, we didn’t have to define or map anything, as the developers have already done their best for us. Here, for example, is a link to all properties (including Jira fields) that are predefined in the language, and here you can see a list of all standard variables, which can also be safely accessed without additional settings.
Thus, the first condition is to see if the type of the parent task is Story. If the first condition isn’t satisfied, then the type of the parent task isn’t Story, or it doesn’t exist at all. And this brings us to the second condition: EpicLink.
In fact, this is when we check if the “Epic Link” Jira field is filled in (that is, we check its existence). The EpicLink variable is also standard and doesn’t need to be mapped. It turns out that our condition is satisfied if the task has Epic Link.
And the third option is when none of the conditions is met, that is, the task has neither a parent nor Epic Link. In this case, we don’t display anything and leave the field empty. This is done automatically since we won’t get any of the results.
We figured out the conditions, now let’s move on to the results. In both cases, it’s a string with text and special formatting.
Result 1 (if the parent is Story):
"""{color:green}[${Parent.Issuetype}]{color} ${Parent.Summary}"""
Result 2 (if there’s Epic Link):
"""{color:#713A82}[${EpicLink.Issuetype}]{color} ${EpicLink.EpicName}"""
Both results are similar in structure: they both consist of triple quotes “”” at the beginning and end of the output string, color specification in the opening {color: COLOR} and closing {color} blocks, as well as operations done through the $ symbol. Triple quotes tell the structure that inside there will be variables, operations, or formatting blocks (such as colors).
For the result of the first condition, we:
- Transfer the type of the parent task ${Parent.Issuetype}
- Enclose it in square brackets “[…]”
- Highlight everything in green, wrapping this expression [${Parent.Issuetype}] into the color selection block {color:green}…{color}, where we wrote “green”
- And one last thing, add the name of the parent task separated by a space ${Parent.Summary}.
Thus, we get the string “[Story] Some task name.” As you might have guessed, Summary is also a standard variable. To make the scheme for constructing such strings clearer, let me share an image from the official documentation.
In a similar way, we collect the string for the second result, but set the color through the hex code. I figured out that the color of the epic was “#713A82” (in the comments, by the way, you can suggest a more accurate color for Epic). Don’t forget about the fields (properties) that change for Epic. Instead of “Summary”, use “EpicName”, instead of “Parent”, use “EpicLink”.
As a result, the scheme of our formula can be represented as a table of conditions.
Condition: Parent-task exists, and its type is Story.
Result: Line with green type of parent-task and its name.
Condition: The Epic Link field is filled out.
Result: Line with the epic color of the type and its name.
By default, the “General” display option is selected in the field, and if you don’t change it, the result will look like plain text without changing the color and identifying the blocks. If you change the display format to “Wiki Markup”, the text will be transformed.
Now, let’s get acquainted with variables that aren’t related to Jira fields — local variables.
Calculating the amount of Story points with color indication
Problem
From the previous example, you learned that we’re working with tasks of the Story type, which have subtasks. This gives rise to a special case with estimates. To get a Story score, we summarize the scores of its subtasks, which are estimated in abstract Story points.
The approach is unusual, but it works for us. So, when Story doesn’t have an estimate, but subtasks do, there is no problem, but when both Story and subtasks have an estimate, the standard option from Structure, “Σ Story Points”, works incorrectly.
This is because the estimate of Story is added to the sum of sub-tasks. As a result, the wrong amount is displayed in Story. We would like to avoid this and add an indication of inconsistency with the established estimate in Story and the sum of subtasks.
Proposed solution
We need several conditions, since it all depends on whether the estimation is set in Story.
So the conditions are:
When Story has no estimate, we display the sum of subtasks estimate in orange to indicate that this value hasn’t yet been set in Story
If Story has an estimate, then check if it corresponds to the sum of subtasks estimate:
- If it doesn’t match, then color an estimate in red, and write the correct amount next to it in brackets
- If an estimate and the sum match, just write an estimate in green
The wording of these conditions can be confusing, so let’s express them in a scheme.
Structure features used
- Variable mapping
- Local variables
- Methods of aggregation
- Conditions
- Text with formatting
A code example
with isEstimated = storypoints != undefined:
with childrenSum = sum#children{storypoints}:
with isStory = issueType = "Story":
with isErr = isStory AND childrenSum != storypoints:
with color = if isStory :
if isEstimated :
if isErr : "red"
else "green"
else "orange":
if isEstimated : """{color:$color}$storypoints{color}
${if isErr :""" ($childrenSum)"""}"""
else """{color:$color}$childrenSum{color}"""
An analysis of the solution
Before diving into the code, let’s transform our scheme into a more “code-like” way to understand what variables we need.
From this scheme we see that we’ll need:
Condition variables:
- isEstimated (availability of the estimation)
- isError (correspondence of Story estimate and the sum)
One variable of text color — color
Two variables of estimation:
- sum (the sum of Sub-tasks estimation)
- sp (Story points)
Moreover, the color variable also depends on a number of conditions, for example, on the availability of an estimate and on the type of task in the line (see the scheme below).
So, to determine the color, we’ll need another condition variable, isStory, which indicates whether the task type is Story.
The sp variable (storypoints) will be standard, meaning it will automatically map to the appropriate Jira field. We should define the rest of the variables by ourselves and they will be local to us.
Now let’s try to implement the schemes in code. First, let’s define all the variables.
with isEstimated = storypoints != undefined:
with childrenSum = sum#children{storypoints}:
with isStory = issueType = "Story":
with isErr = isStory AND childrenSum != storypoints:
The lines are united by the same syntax scheme: the with keyword, the variable name, and the colon symbol “:” at the end of the line.
The with keyword is used to denote local variables (and custom functions, but more on that in a separate example). It tells the formula that next goes a variable that doesn’t need to be mapped. The colon “:” flags the end of the variable definition.
Thus, we create the isEstimated variable (reminder, that case isn’t important). We will store either 1 or 0 in it, depending on whether the story points field is filled. The storypoints variable is mapped automatically because we haven’t created a local variable with the same name before (e.g., with storypoints = … :).
The undefined variable denotes the non-existence of something (as null, NaN and the like in other languages). Therefore, the expression storypoints != undefined can be read as a question: “Is the story points field filled out?”.
Next, we should determine the sum of the story points of all child tasks. To do this, we create a local variable: childrenSum.
with childrenSum = sum#children{storypoints}:
This sum is calculated through the aggregation function. (You can read about functions like this in the official documentation.) In a nutshell, Structure can perform various operations with tasks, taking into account the hierarchy of the current view.
We use the sum function, and in addition to it, using the “#” symbol, we pass the clarification children, which limits the calculation of the sum only to any child tasks of the current line. In curly brackets, we indicate which field we want to summarize — we need an estimate in storypoints.
The next local variable, isStory, stores a condition: whether the task type in the current line is a Story.
with isStory = issueType = "Story":
We turn to the issueType variable, familiar from the past example, that is, the type of task that maps to the desired field by itself. We’re doing this because it’s a standard variable and we haven’t previously defined it through with.
Now let’s define the isErr variable — it signals a discrepancy between the subtask sum and the Story estimate.
with isErr = isStory AND childrenSum != storypoints:
Here we’re using the isStory and childrenSum local variables we created earlier. To signal an error, we need two conditions to be met simultaneously: the issue type is Story (isStory) and (AND) the sum of children points (childrenSum) is not equal (!=) to the set estimate in the task (storypoints). Just like in JQL, we can use link words when creating conditions, like AND or OR.
Note, that for each of the local variables there is a “:” symbol at the end of the line. It should be at the end, after all the operations that define the variable. For example, if we need to break the definition of a variable into several lines, then the colon “:” is placed only after the last operation. As in the example with the color variable — the color of the text.
with color = if isStory :
if isEstimated :
if isErr : "red"
else "green"
else "orange":
Here we see a lot of “:”, but they play different roles. The colon after if isStory is the result of the isStory condition. Let’s recall the construction: if condition : result. Let’s present this construction in a more complex form, which defines a variable.
with variable =
(if condition: (if condition2 : result2 else result3) ):
It turns out that if condition2 : result2 else result3 is, as it were, the result of the first condition, and at the very end there is a colon “:”, which completes the definition of the variable.
At first glance, the definition of color may seem complicated, although, in fact, we’ve described here the color definition scheme presented at the beginning of the example. It’s just that as a result of the first condition, another condition begins — a nested condition, and another one in it.
But the final result is slightly different from the previously presented scheme.
if isEstimated : """{color:$color}$storypoints{color}
${if isErr :""" ($childrenSum)"""}"""
else """{color:$color}$childrenSum{color}"""
We don’t have to write “{color}$sp’’ twice in the code, as it was in the scheme; we’ll be smarter about things. In the branch, if the task has an estimate, we will always display {color: $color}$storypoints{color} (that is, just an estimate in story points in the needed color), and if there is an error, then after a space, we will supplement the line with the sum of the subtasks estimate: ($childrenSum).
If there is no error, it won’t be added. I also draw your attention to the fact that there is no “:” symbol, since we don’t define a variable, but display the final result through a condition.
We can evaluate our work in the image below in the field “∑SP (mod)”. The screenshot specifically shows two additional fields:
- “Story Points” — an estimate in story points (standard Jira-field).
- “∑ Story Points” — a Structure standard custom field, which calculates the amount incorrectly.
With the help of these examples, we’ve analyzed the main features of the structure language that will help you solve most of the problems. Let’s now look at two more useful features, our functions and arrays. We’ll see how to create our own custom function.
Last changes
Problem
Sometimes there are many tasks in a sprint and we may miss small changes in them. For example, we may miss a new subtask or the fact that one of the stories has moved to the next stage. It would be nice to have a tool notifying us about the latest important changes in tasks.
Proposed solution
We’re interested in three types of task status changes that have occurred since yesterday: we started working on the task, a new task appeared, the task is closed. Additionally, it will be useful to see that the task is closed with the resolution “Won’t Do”.
To do this, we will create a field with a string of emojis that are responsible for the latest changes. For example, if a task was created yesterday and we started working on it, then it will be marked with two emojis: “In progress” and “New task”.
Why do we need such a custom field, if several additional fields can be displayed, for example, the date of transition to the “In Progress” status or a separate “Resolution” field? The answer is simple — people perceive emojis easier and faster than text, which is located in different fields and needs to be analyzed. The formula will collect everything in one place and analyze it for us, which will save us effort and time for more useful things.
Let’s determine what the different emoji will be responsible for:
- *️⃣ is the most common way to mark a new task
- ✅ marks a completed task
- ❌ indicates a task you decided to cancel (“Won’t Do”)
- 🚀 means that we decided to start work on the task (this emoji is suitable for our team, it may be different for you)
Structure features used
- Variable mapping
- Expr language methods
- Local variables
- Conditions
- Our own function
A code example
if defined(issueType):
with now = now():
with daysScope = 1.3:
with workDaysBetween(today, from)=
(
with weekends = (Weeknum(today) - Weeknum(from)) * 2:
HOURS_BETWEEN(from;today)/24 - weekends
):
with daysAfterCreated = workDaysBetween(now,created):
with daysAfterStart = workDaysBetween(now,latestTransitionToProgress):
with daysAfterDone = workDaysBetween(now, resolutionDate):
with isWontDo = resolution = "Won't Do":
with isRecentCreated = daysAfterCreated >= 0
and daysAfterCreated <= daysScope
and not(resolution):
with isRecentWork = daysAfterStart >= 0 and daysAfterStart <= daysScope :
with isRecentDone = daysAfterDone >= 0 and daysAfterDone <= daysScope :
concat(
if isRecentCreated : "*️⃣",
if isRecentWork : "🚀",
if isRecentDone : "✅",
if isWontDo : "❌")
An analysis of the solution
To begin with, let’s think about the global variables we need in order to determine the events of interest to us. We need to know, if since yesterday:
- The task has been created
- The status has changed to “In Progress”
- A resolution has been found (and which one)
Using already existing variables alongside new mapping variables will help us to check all these conditions.
- created — the date of task creation
- latestTransitionToProgress — the latest date of transition to the “In Progress” status (we map it as in the previous example)
- resolutionDate — the date of task completion
- resolution — resolution text
Let’s move on to the code. The first line starts with a condition that checks if the task type exists.
if defined(issueType):
This is done through the built-in defined function, which checks for the existence of the specified field. The check is made to optimize the calculation of the formula.
We won’t load Structure with useless calculations, if the line isn’t a task. It turns out that all the code after if is the result, I mean, the second part of the if (condition : result) construction. And if the condition isn’t met, then the code won’t work either.
The next line with now = now(): is also needed to optimize calculations. Further in the code, we’ll have to compare different dates with the current date several times. In order not to do the same calculation several times, we’ll calculate this date once and make it a local variable now.
It would also be nice to keep our “yesterday” separately. Convenient “yesterday” empirically turned into 1.3 days. Let’s make this into a variable: with daysScope = 1.3:.
Now we need to calculate the number of days between two dates several times. For example, between the current date and the work start date. Of course, there is a built-in DAYS_BETWEEN function, which seems to suit us. But, if the task, for example, was created on Friday, then on Monday we won’t see a notice of a new task, since in fact more than 1.3 days have passed. In addition, the DAYS_BETWEEN function only counts the total number of days (that is, 0.5 days will turn into 0 days), which also doesn’t suit us.
We’ve formed a requirement — we need to calculate the exact number of working days between these dates; and a custom function will help us with this.
Its defining syntax is very similar to the syntax for defining a local variable. The only difference and the only addition are the optional enumeration of arguments in the first brackets. The second brackets contain the operations that will be performed when our function is called. This definition of the function isn’t the only possible one, but we will use this one (others can be found in the official documentation).
with workDaysBetween(today, from)=
(
with weekends = (Weeknum(today) - Weeknum(from)) * 2:
HOURS_BETWEEN(from;today)/24 - weekends
):
Our custom workDaysBetween function will calculate the working days between today and from dates, which are passed as arguments. The logic of the function is very simple: we count the number of days off and subtract them from the total number of days between the dates.
To calculate the number of days off, we need to find out how many weeks have passed between today and from. To do this, we calculate the difference between the numbers of each of the weeks. We’ll obtain this number from the Weeknum function, which provides us with the week number from the beginning of the year. Multiplying this difference by two, we get the number of passed days off.
Next, the HOURS_BETWEEN function counts the number of hours between our dates. We divide the result by 24 to get the number of days, and subtract the days off out of this number, so we get the workdays between the dates.
Using our new function, let’s define a bunch of auxiliary variables. Note that some of the dates in the definitions are global variables, which we talked about at the beginning of the example.
with daysAfterCreated = workDaysBetween(now,created):
with daysAfterStart = workDaysBetween(now,latestTransitionToProgress):
with daysAfterDone = workDaysBetween(now, resolutionDate):
To make the code convenient for reading, let’s define variables that store the results of the conditions.
with isWontDo = resolution = "Won't Do":
with isRecentCreated = daysAfterCreated >= 0
and daysAfterCreated <= daysScope
and not(resolution):
with isRecentWork = daysAfterStart >= 0 and daysAfterStart <= daysScope :
with isRecentDone = daysAfterDone >= 0 and daysAfterDone <= daysScope :
For the isRecentCreated variable, I added an optional condition and not(resolution), which helps me simplify the future line, because if the task is already closed, then I’m not interested in information about its recent creation.
The final result is constructed via the concat function, concatenating the lines.
concat(
if isRecentCreated : "*️⃣",
if isRecentWork : "🚀",
if isRecentDone : "✅",
if isWontDo : "❌")
It turns out that the emoji will be in the line only when the variable in the condition is equal to 1. Thus, our line can simultaneously display independent changes to the task.
We’ve touched on the topic of counting working days without days off. There is another problem related to this, which we’ll analyze in our last example and at the same time get acquainted with arrays.
Calculation of working hours, excluding days off
Problem
Sometimes we want to know how long a task has been running, excluding days off. This is necessary, for example, to analyze the released version. To understand why we need days off. Except one was running from Monday to Thursday, and the other one, from Friday to Monday. In such a situation, we cannot state that the tasks are equivalent, although the difference in calendar days tells us the opposite.
Unfortunately, Structure “out of the box” doesn’t know how to ignore days off, and the field with the “Time in status…” option produces a result regardless of Jira settings — even if Saturday and Sunday are specified as days off.
As a result, our goal is to calculate the exact number of working days, ignoring days off, and take into account the impact of status transitions on this time.
And what have statuses got to do with it? Let me answer. Suppose we calculated that between March 10 and March 20, the task was at work for three days. But out of these 3 days, it was on pause for a day and in the review for a day and a half. It turns out that the task was at work for only half a day.
The solution from the previous example doesn’t suit us because of the problem of switching between statuses, because the custom workDaysBetween function takes into account only the time between two selected dates.
Proposed solution
This problem can be solved in different ways. The method in the example is the most expensive in terms of performance, but the most accurate in terms of counting days off and statuses. Note that its implementation only works in the Structure version older than 7.4 (December 2021).
So, the idea behind the formula is as follows:
- We need to find out how many days have passed from the start to the completion of the task
- We make an array out of this, that is, a list of days between the start and end of our work on the task
- Keep only days off in the list
- Out of these days off, we keep only those when the task was in the “In Progress” status (the feature from version 7.4 “Historical Value” will help us here)
- Now in the list, we have only those days off that coincided with the “In Progress” period
- Separately we’re finding out the total duration of the “In Progress” status (through the built-in Structure option “Time in status…”);
- Subtract from this time the number of previously obtained days off
Thus, we will get the exact time of work on the task, ignoring days off and transitions between extra statuses.
Structure features used
- Variable mapping
- Expr language methods
- Local variables
- Conditions
- An internal method (our own function)
- Arrays
- Access to the history of the task
- Text with formatting
A code example
if defined(issueType) :
if status != "Open" :
with finishDate =
if toQA != Undefined : toQA
else if toDone != Undefined : toDone
else now():
with startDate = DEFAULT(toProgress, toDone):
with statusWeekendsCount(dates, status) =
(
dates.filter(x -> weekday(x) > 5 and historical_value(this,"status",x)=status).size()
):
with overallDays = round(hours_between(startDate,finishDate)/24):
with sequenceArray = SEQUENCE(0,overallDays):
with datesArray = sequenceArray.map(DATE_ADD(startDate,$,"day")):
with progressWeekends = statusWeekendsCount(datesArray, "in Progress"):
with progressDays = (timeInProgress/86400000 - progressWeekends).round(1):
with color = if(
progressDays = 0 ; "gray"
; progressDays > 0 and progressDays <= 2.5; "green"
; progressDays > 2.5 and progressDays <= 4; "orange"
; progressDays > 4; "red"
):
"""{color:$color}$progressDays d{color}"""
An analysis of the solution
Before transferring our algorithm to the code, let’s facilitate the calculations for Structure.
if defined(issueType) :
if status != "Open" :
If the line isn’t a task or its status is “Open”, then we will skip those lines. We are only interested in tasks that have been launched to work.
To calculate the number of days between dates, we must first determine these dates: finishDate and startDate.
with finishDate =
if toQA != Undefined : toQA
else if toDone != Undefined : toDone
else now():
with startDate = DEFAULT(toProgress, toDone):
We’ll assume that the task completion date (finishDate) is:
- Either the date when the task was transferred to the “QA” status
- Either the date of transition to “Closed”
- Or if the task is still in “In Progress”, then today’s date (to understand how much time has passed)
Work start date startDate is determined by the date of transition to the “In Progress” status. There are cases when the task gets closed without going to the in-work stage. In such cases, we consider the closing date as the start date, so, the result is 0 days.
As you might have guessed toQA, toDone and toProgress are variables that need to be mapped to the appropriate statuses as in the first and previous examples.
We also see the new DEFAULT(toProgress, toDone) function. It checks if toProgress has a value, and if not, it uses the value of the toDone variable.
Next comes the definition of the statusWeekendsCount custom function, but we’ll return to it later, since it’s closely related to lists of dates. It’s better to go straight to the definition of this list, so that later we can understand how to apply our function to it.
We want to get a list of dates in the following form: [startDate (let’s say 11.03), 12.03, 13.03, 14.03 … finishDate]. There is no simple function that would do all the work for us in Structure. So let’s resort to a trick:
- We’ll create a simple list from a sequence of numbers from 0 to the number of days in work, that is, [0, 1, 2, 3 … n days in work]
- Add the start date of the task to each number (i.e. day). As a result, we get a list (array) of the required type: [start + 0 days, start + 1 day, start + 2 days … start + n days of work].
Now, let’s see how we can implement it in the code. We’ll be working with arrays.
with overallDays = round(hours_between(startDate,finishDate)/24):
with sequenceArray = SEQUENCE(0,overallDays):
with datesArray = sequenceArray.map(DATE_ADD(startDate,$,"day")):
We count how many days the work on a task will take. As in the previous example, through division by 24 and the hours_between(startDate,finishDate)function. The result is written in the overallDays variable.
We create an array of the numbers sequence in the form of the sequenceArray variable. This array is constructed via the SEQUENCE(0,overallDays)function, which simply makes an array of the desired size with a sequence from 0 to overallDays.
Next comes the magic. One of the array functions is map. It applies the specified operation to each element of the array.
Our task is to add the start date to each number (that is, the number of the day). The DATE_ADD function can do this, it adds a certain number of days, months or years to the specified date.
Knowing this, let’s decrypt the string:
with datesArray = sequenceArray.map(DATE_ADD(startDate, $,"day"))
To each element in the sequenceArray, the .map()function applies DATE_ADD(startDate, $, “day”).
Let’s see what is passed in the arguments for DATE_ADD. The first thing is startDate, the date to which the desired number will be added. This number is specified by the second argument, but we see $.
The $ symbol denotes an array element. The structure understands that the DATE_ADD function is applied to an array, and therefore instead of $ there will be the desired array element (that is, 0, 1, 2 …).
The last argument “day” is an indication that we add a day, since the function can add a day, month, and year, depending on what we specify.
Thus, the datesArray variable will store an array of dates from the start of work to its completion.
Let’s get back to the custom function we missed. It will filter out extra days and calculate the remainder. We described this algorithm at the very beginning of the example, before analyzing the code, namely in paragraphs 3 and 4 about filtering out days off and statuses.
with statusWeekendsCount(dates, status) =
(
dates.filter(x -> weekday(x) > 5 and historical_value(this,"status",x)=status).size()
):
We’ll pass two arguments to the custom function: an array of dates, let’s call it dates, and the required status — status. We apply the .filter()function to the transferred dates array, which keeps only those records in the array that have passed through the filter condition. In our case, there are two of them, and they are combined through and. After the filter, we see .size(), it returns the size of the array after all operations on it are done.
If we simplify the expression, we get something like this: array.filter(condition1 and condition2).size(). So, as a result, we got the number of days off suitable for us, that is, those days off that passed the conditions.
Let’s take a closer look at both conditions:
x -> weekday(x) > 5 and historical_value(this,"status",x)=status
The expression x -> is just part of the filter syntax, indicating that we will call the element of the array x . Therefore, x appears in each condition (similar to how it was with $). It turns out that x is each date from the transferred dates array.
The first condition, weekday(x) > 5, requires that the weekday of date x (that is, each element) be greater than 5 — it’s either Saturday (6) or Sunday (7).
The second condition uses historical_value.
historical_value(this,"status",x) = status
That is a feature of Structure of version 7.4.
The function accesses the history of the task and searches for a specific date in the specified field. In this case, we are searching for date x in the “status” field. The this variable is just part of the function syntax, it’s mapped automatically and represents the current task in the line.
Thus, in the condition, we compare the transferred status argument and the “status”field, which is returned by the historical_value function for each date x in the array. If they match, then the entry remains in the list.
The final touch is the use of our function to count the number of days in the desired status:
with progressWeekends = statusWeekendsCount(datesArray, "in Progress"):
with progressDays = (timeInProgress/86400000 - progressWeekends).round(1):
First, let’s find out how many days off with the “in Progress” status got in our datesArray. That is, we pass our list of dates and the desired status to the custom function statusWeekendsCount. The function takes away all weekdays and all days off in which the status of the task differs from the “in Progress” status and returns the number of days remaining in the list.
Then we subtract this amount from the timeInProgress variable, which we map through the “Time in status …” option.
The number 86400000 is the divisor that will turn milliseconds into days. The .round(1) function is needed to round the result to tenths, for example to “4.1”, otherwise you can get this type of entry: “4.0999999 …”.
To indicate the length of the task, we introduce the color variable. We’ll change it depending on the number of days spent on the task.
- Gray — 0 days
- Green — more than 0 but less than 2.5 days
- Red — from 2.5 to 4 days
- Red — more than 4 days
with color = if(
progressDays = 0 ; "gray"
; progressDays > 0 and progressDays <= 2.5; "green"
; progressDays > 2.5 and progressDays <= 4; "orange"
; progressDays > 4; "red"
):
And the final line with the result of calculated days:
"""{color:$color}$progressDays d{color}"""
Our result will look like in the image below.
By the way, in the same formula, you can display the time of any status. If, for example, we pass the “Pause” status to our custom function, and map the timeInProgress variable through “Time in … — Pause”, then we will calculate the exact time in the pause.
You can combine statuses and make an entry like “wip: 3.2d | rev: 12d”, that is, calculate the time in work and the time in review. You’re only limited by your imagination and your workflow.
Conclusion
We presented an exhaustive number of features of this formula language that will help you do something similar or write something completely new and interesting for analyzing Jira tasks.
I hope the article helped you figure out the formulas, or at least got you interested in this topic. I don’t claim that I have “the best code and algorithm”, so if you have ideas on how to improve the examples, I’d be glad if you share them!
Of course, you need to understand that no one will tell you about formulas better than the ALM Works developers. Therefore, I’m attaching links to their documentation and webinars. And if you start working with custom fields, check them out often to see what other features you can use.