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]


Aggregates by 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)


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)


Text formulas (coming soon)

Did this answer your question?