Structure's Formulas feature is now available for Atlassian Cloud, allowing you to perform real-time calculations right within Jira — instead of using manual Excel exports to compare data.

The Formulas feature is popular among Structure on-prem users, and now cloud users can access similar capabilities for revealing project insights. Jira data holds a wealth of information about your projects, and Formulas help you leverage it — without having to leave Jira.

We want to show you some of the insights that Formulas can put within easy reach. Read on for key use-cases and a list of resources to better understand what Formulas can do for you.

Note for our on-prem users: Scroll down to the Comparison section for a rundown of differences between on-prem/cloud Formulas, and see opportunities for sharing your custom formulas!

What can I do with Formulas?

Formulas allow you to perform simple or complex calculations based on the data in Jira. Need to compare the actual time spent to the original estimate? Go ahead. Need to visualize story points as hours? It’s a simple calculation. Need to build a RICE scoring model to prioritize upcoming features? Use our preset formulas available in Structure's Add Columns menu — use it as-is, or modify it to fit your specific business needs.

In the screenshot below we've included another example — a formula for WSJF (Weighted Shortest Job First).

Everyone’s needs are unique, which is what makes formulas so useful — you get to decide what information you need to see and how it should be calculated.

Experienced users may create sophisticated formulas for complex metrics, but we wanted to introduce some extremely simple examples to help newcomers. These options can help you put Formulas to work right away.

Identify forgotten issues

This formula will tell you how long it’s been since any work has been done on each issue, so you can see at a glance which issues haven’t had much attention lately.

    DAYS_BETWEEN(updated, NOW())

Spot scope creep

Here are a couple of simple strategies for identifying potential scope creep.

Original estimate vs. current estimate

This formula will return the ratio of current estimate to original estimate. If an issue doesn’t have an original estimate, it will let you know that too.

    IF originalEstimate : (timeSpent + remainingEstimate) / originalEstimate
    ELSE : "not estimated"

Recently added issues

This formula will identify any issues that have been added since you committed to a plan. In this example, we used four weeks as our timeline, but you can replace the “-4w” with any length of time.

    IF JQL{created < "-4w"} : "scope creep"

Check for mismatched versions

This formula checks that each parent issue and all its children are assigned to the same Fix version. If not, it returns the message “Version Mismatch."

    WITH parentVersion = PARENT{fixVersion}:
        IF fixVersion != parentVersion: "Version Mismatch"

Check for matching components

Our last example performs a check to make sure child issues have the same components as their parent issues — and, if not, lets you know which components don’t match.

    WITH childComponents = SPLIT(JOIN#strict#subtree#distinct{components}, ","):
        IF childComponents.size()=0: " "
        ELSE IF contains_all(components, childComponents): "Children Match"
        ELSE : childComponents.FILTER(!components.CONTAINS($))       

This one is a bit more complicated, but it’s a great example of a formula that uses aggregate functions and modifiers — and it’s a very useful formula!

Here’s how it works:

1. It aggregates the component values for all the child items, using the following modifiers:

• #subtree checks every level below the current
• #strict removes the current row
• #distinct ignores any duplicates

2. This returns a string, so the SPLIT function turns it into an array of component values.

3. If this array is empty (i.e., no children or no components) the formula returns nothing ( " " )

4. If there are items in the array, it checks whether the children’s components match the list of components for the current row (the parent item):

• If they do, the formula returns: "Match"
• If not, it returns the components that did not match

Additional Resources

Formulas can be very simple (like the examples above) or incredibly complex (like Weighted Shortest Job First). If you’ve ever created a formula in Excel, our Expr formula language was designed to be similar and easy to use. But whether you’re a complete formula newbie or looking to become an Expr expert, our documentation is full of resources to help you get the most out of Structure Formulas:

• Creating a Formula – this guide will walk you through how to add a Formula column to your structure, enter your formula, and handle any errors.
• Expr Language – a general overview of the Expr language and what sort of functions are possible with it.
• Expr Function Reference – a categorized list of all the functions available in Expr.
• Aggregate Function Reference – a list of all aggregate functions (functions that are calculated for an item and all its sub-items) available in Expr.
• Bundled Formulas – Structure comes with a number of predefined formulas, which you can use as is or customize for your specific use case.

Formulas on Cloud vs. Formulas on DC/Server

Formulas have been around in the on-prem version of Structure for some time now, and they’ve developed into an extremely robust feature. We’ve included what we think are the best elements of Formulas in our initial cloud release, but we still have some work ahead of us to make the rest of our Formula functionality available on cloud.

As of this article’s publication, in Structure for Jira Cloud:

• Formulas are only available in Formula columns
• Only Jira fields can be used as variables
• Jira duration is not supported
• Wiki Markup is not available

For a complete list of differences between Structure versions, see our comparison sheet.

If you come up with a great formula you think others could benefit from, share it on our Structure Community! We’re sharing various formulas in the #structure-cloud channel, so join the community and be part of the conversation too. And if using Google forms isn't permitted at your workplace, please contact us via community@almworks.com.