If you have a space symbol in Field or Collection name, put the name in square brackets [ ] , like this:

[User Stories].[Total Effort]

Basic math operations

Nothing fancy here, usual operators are supported (*, -, +, /)

(Effort * Risk) / Complexity.Value
Velocity - [Assigned Effort]

Access Collection and Fields

Use dot notation to access Collections and Fields:

Epic.Estimate
Epic.Product.Value * [Own Value]

Collections

For example, you have Product and several Features linked to every Product. On a Product level you may want to calculate various metrics for Features. Here is where collection aggregates will help:

  • Sum(Number Field Name)
  • Avg(Number Field Name)
  • Count()
  • Max(Number Field Name)
  • Min(Number Field Name)

For Product Type you may use these examples:

Features.Count()
Features.Sum(Estimate)
Features.Max(Estimate)
Features.Avg(Estimate)


Join(text field, delimiter)

Combines text values from a collection of entities into a single text. For example, returns string of assignees

Assignees.Join(Name, ', ') → "Michael, Teddy, Jerry"

CountUnique(expression)

Counts the number of unique values in a collection of entities

How many teams work on Feature across all User Stories?

Stories.CountUnique(Team.PublicId)

Dates

In general you can do operations with Dates like with Numbers. For example, here is how you define duration of a project in days:

ToDays(Planned.End - Planned.Start)

Note that you can just subtract dates. The result of these operations is a Duration object that has format Duration(days, hours, minutes, seconds). For example, it can be Duration(10, 0, 0, 0) and this is exactly 10 days. 

Then you have to apply functions like ToDays or ToHours to convert duration object into number of days or hours.

Today()

Returns current date.

ToDays(Today() - Planned.Start)

ToDays(Duration)

Convert a time duration into a number of days.

ToDays(Planned.End - Planned.Start)

ToHours(Duration)

Convert a time duration into a number of hours.

ToHours(Planned.End - Planned.Start)

ToMinutes(Duration)

Convert a time duration into a number of minutes.

ToMinutes(Planned.End - Planned.Start)

ToSeconds(Duration)

Convert a time duration into a number of seconds.

ToSeconds(Planned.End - Planned.Start)

Duration(days, hours, minutes, seconds)

Create a time duration.

Duration(10, 0, 0, 0)

Days(number) 

Returns Duration for days.
For example, you want to create a field that will show date 2 days before deadline. Later you can use this field to highlight cards on boards. Here is how you do it:

[Due Date] - Days(2)

Hours(number)

Returns Duration for hours.

Minutes(number)

Returns Duration for minutes.

Seconds(number)

Returns Duration for seconds.

Min/Max

These functions can be used inside collections. Here is the case:

  • A Feature is decomposed into Stories
  • The Stories are planned using Sprints
  • Each Sprint has a start date and an end date

Could I automatically calculate the start and the end date of the Feature to show on a Timeline? Yes, you could:

Feature.[Start Date] = Stories.Min(Sprint.[Start Date])
Feature.[End Date] = Stories.Max(Sprint.[End Date])


Filter inside collections

Use Filter function (with AND and OR operators) to extract subsets from collections.

Sum of Effort of all not completed Features:

Features.Filter(State.Final != true).Sum(Effort)


Count of all high complexity non-estimated Features: 

Features.Filter(Effort = 0 AND Complexity.Name = "High").Count()


Logic (If, and, or)

You can use If  to create various interesting formulas. 

If(condition, true result, false result)

Returns some result based on condition. Here are some examples:


Cycle Time calculation:


If([Planned Dates].End > Today(),
ToDays(Today() - [Planned Dates].Start),
ToDays([Planned Dates].End - [Planned Dates].Start))


Generate some text field based on conditions

If((Confidence > 10) and (Reach > 10),"Important","So-so")


Nest if functions:

If([RICE Score] >= 15,"Cool", If([RICE Score] >= 10,"OK","Bad"))


Generate checkbox (for example, you may create "Fix ASAP" field with this formula)

If((Risk = "High") and (Severity = "High"), true, false)


IsEmpty

Checks whether value is empty

IsEmpty("") → true

Text formulas

Use + to concatenate strings. Some examples:

Name + " " + [Public Id]

Project.Abbreviation + "-" + [Public Id]

"https://www.google.com/maps/search/?api=1&query=" + Address

Note that you can select Text or URL as a formula type.

Length(text)
Calculates the number of characters in the text

Length("This phrase has 29 characters")

Lower/Upper(text)
Converts the text to lower/upper case

Upper(Name) + " " + Upper(Abbreviation)

Left(text, numberOfCharacters)
Extracts the desired number of starting characters from the text

Left("Fibery rules", 6)

Right(text, numberOfCharacters)
Extracts the desired number of ending characters from the text

Right("Fibery rules", 5)

Trim(text)
Removes starting and ending spaces from the text

Trim(LastName)

EndsWith(text, suffix)
Checks if text ends with a suffix

EndsWith("Fibery rules", "rules")

StartsWith(text, prefix)
Checks if text starts with a prefix

EndsWith("Fibery rules", "Fibery")

MatchRegex(text, regularExpression)
Checks if text matches a regular expression

MatchRegex("Fibery", "F.b")

Replace(text, searchFor, replacementText)
Substitutes a matching part of the text

Replace("Fibery is slow", "slow", "fast")

ReplaceRegex(text, regularExpression, replacementText)
Substitutes regular expression matches in the text

ReplaceRegex("It costs $2000", "(\d+)", "1000")

Middle(text, startAt, numberOfCharacters)

Extracts the desired number of characters from the middle of the text

Middle("Umbrella", 5, 4)

Find(text, searchFor)

Finds the first occurrence of the string in the text

Find("Where's Waldo?", "Waldo") → 8

Misc.

Greatest(value1[, value2, value3,…])

Calculates the largest number or the latest date across several values

Greatest(Appointments.Max([Creation Date], Drugs.Max([Creation Date])), Comments.Max([Creation Date]))

Least(value1[, value2, value3,…])

Calculates the smallest number or the earliest date across several values

Least(Appointments.Max([Creation Date], Drugs.Max([Creation Date])), Comments.Max([Creation Date]))
Did this answer your question?