β†’ Here is the full formulas reference

What is inside the Article (overview list)

Collections (sum-min-max-avg-count)

  • Sum:
    Spent Effort on the Project Level
    Sprint Capacity on the Sprint Level
    Total Revenue on the Project Level
    Billable Total using Time Tracking App
    Total Expenses on the Project Level

  • Count:
    Number of tasks for the project
    Number of stories for the sprint
    Anything you want

  • Get Average:
    OKR progress
    Delivery cost
    Goods price
    Effort per task

  • Find maximum:
    The highest price
    The most complicated task (by effort)
    The task, that was planned to finish last

  • Find the minimum:
    The lowest price
    The easiest task (by effort)
    The task, that was planned to start first

Filter inside collections (filter data for sum-min-max-avg-count)

  • Spent Effort on the Project Level for Tasks, that are in "Done" State

  • Progress on the Project Level, based on the total amount of Tasks and finished one **(pro level)

  • Remaining Effort on the Project Level for Tasks, that are still not done yet **(pro level)

  • Total Revenue on the Project Level from Invoices, that are Paid already

  • Spent Effort on the Project Level for Tasks, that has Planned Start before Today

Dates Operations

- calculate planned duration in days
- calculate actual duration in days

- calculate how many days are left before the start
- calculate how many days are left before the deadline

- calculate planned duration in hours/minutes/seconds
- calculate actual duration in hours/minutes/seconds

- create a field that will show date N days before the deadline (Deadline - N days)
- create a field, that will show a Review date (Actual end + 7 days)

Logic (If, and, or)

- mark urgent tasks based on deadline coming
- check whether your employee has too many tasks
- cycle time

Cases

Collections (sum-min-max-avg-count)

Difficulty level: ⭐

πŸ’‘When it can be helpful? :

Calculate (Sum):

Types

Field to sum

Formula syntax

Spent Effort on the Project Level

Project ➑ Task

Effort

Tasks.Sum(Effort)

Sprint Capacity on the Sprint Level

Sprint ➑ Story

Effort

Stories.Sum(Effort)

Total Revenue on the Project Level

Project ➑ Invoice

Invoice Value

Invoices.Sum(Invoice Value)

Billable Total using Time Tracking App

Project ➑ Time Log

Hours

Time Logs.Sum(Hours)

Total Expenses on the Project Level

Project ➑ Expenses

Spent

Expenses.Sum(Spent)

ο»Ώ

Now, let's take the first row as an example and create these Formulas step-by-step πŸ’ͺ:

We will calculate spent Effort on the Project Level ( Project ➑ Task )

Schema:

Data:

Formula text ( Formula field must be created on the Project level):

Tasks.Sum(Effort)

Formula explanation:

Notes:

  • If you have an existing formula (like the one used above), and then change a field name, Fibery will automatically update your formula to reflect the new field name.

  • Please, check that your Child Type has a field, you are going to work with 🧐

Similar Formula syntax works for these Aggregation Numerical calculations:
οΏΌ

Function

Formula syntax

Avg


Returns the avg of a series of numbers
- average OKR progress
- average delivery cost
- average goods price
- average effort per task

Key Results.Avg(Progress)

Deliveries.Avg(Cost)

Goods.Avg(Price)

Task.Avg(Effort)

Count


Returns the count of values
- count number of tasks for the project
- count number of stories for the sprint
- count anything you want

Tasks.Count()

Stories.Count()

Anythingyouwant.Count()

Note: since you are counting the total number of records, not the values from the fields, the Count formula will always have "empty" parentheses and look like TypeName.Count()

Max


Returns the max of a series of numbers
- check the highest price
- check the most complicated task (by effort)
- check the task, that was planned to finish last

Goods.Max(Price)

Tasks.Max(Effort)

Tasks.Max(Planned.End)

Min

Returns the min of a series of numbers

- check the lowest price
- check the easiest task (by effort)
- check the task, that was planned to start first

Goods.Min(Price)

Tasks.Min(Effort)

Tasks.Min(Planned.Start)

Filter inside collections (filter data for sum-min-max-avg-count)

Difficulty level: ⭐⭐

πŸ’‘When it can be helpful? :

Calculate (Filter(Conditions).Sum):

Filter Condition

Formula syntax

Spent Effort on the Project Level for Tasks, that are in "Done" State

Types:

Project ➑ Task

State.Name = "Done"

Field to sum:
Effort

Tasks.Filter(State.Name = "Done").Sum(Effort)

Progress on the Project Level, based on the total amount of Tasks and finished one **(pro level)

Types:

Project ➑ Task

State.Final = true

Field to sum:
Effort

(100 * Tasks.Filter(State.Final = true).Sum(Effort)) / Tasks.Sum(Effort)

Remaining Effort on the Project Level for Tasks, that is still not done yet
**(pro level)

Types:

Project ➑ Task

State.Final != true

Field to sum:
Effort

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

Total Revenue on the Project Level from Invoices, that are Paid already

Types:

Project ➑ Invoice

State.Name = "Paid"

Field to sum:
Revenue

Invoices.Filter(State.Name = "Paid").Sum(Revenue)

Spent Effort on the Project Level for Tasks, that has Planned Start before Today

Types:

Project ➑ Task

Planned.Start < Today()

Field to sum:
Effort

Tasks.Filter(Planned.Start < Today()).Sum(Effort)

Now, let's take the first row as an example and create these Formulas step-by-step πŸ’ͺ:

We will calculate spent Effort on the Project Level, but for those Tasks, that are done already ( Project ➑ Task )

Schema (from our Project Management Template):

Data:

Formula text ( Formula field must be created on the Project level):

Tasks.Filter(State.Name = "Done").Sum(Effort)

Formula explanation:

Notes:

  • If you have an existing formula (like the one used above), and then change a field name, Fibery will automatically update your formula to reflect the new field name.

  • Please, check that your Child Type has a field, you are going to work with 🧐

  • On this formula, we are writing "Done" in quotes - by quotation marks we denote Names. So, if your field, you are going to filter is called is "Amazing", then the formula will look like Tasks.Filter(State.Name = "Amazing").Sum(Effort)

  • Instead of summarizing fields in the end you can use any other formula (count, min, max, avg. and etc.)

Pro users tips πŸ’ͺ:

In the Table, you could see this formula

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

What does State.Final mean?

Well, you can open any State from the Workflow Extension Field, and mark it as a Final one.

And:
- != - is not
- = -is

Dates Operations

Difficulty level: ⭐

Function

Formula syntax

Todays

Convert a time duration into a number of days.
- calculate planned duration in days
- calculate actual duration in days

Note: to use that formula you have to have a Date filed with Duration

ToDays(Planned.End - Planned.Start)

ToDays(Actual.End - Actual.Start)

Today

Date function which returns the current date.
Can be used as a part of another formula, to compare the current date with another one.
- calculate how many days are left before the start
- calculate how many days are left before the deadline

ToDays(Planned.Start - Today())

ToDays(Planned.End - Today())

ToHours/ToMinutes/ToSeconds

Convert a time duration into a number of hours/minutes/seconds.
- calculate planned duration in hours/minutes/seconds
- calculate actual duration in hours/minutes/seconds

ToHours(Minutes(120))

ToMinutes(Seconds(120))

ToSeconds(Actual.End - Actual.Start)

Days/Hours/Minutes/Seconds

Returns Duration for days/hours/minutes/seconds
That formula works well with other formulas
- create a field that will show date N days before the deadline (Deadline - N days)
- create a field, that will show a Review date (Actual end + 7 days)

[Due Date] - Days(2)

Actual.End + Days(7)

Now, let's take the first row as an example and create these Formulas step-by-step πŸ’ͺ:
We will check, how many days did we plan to spend on the Task, and how much have we really spent. That formula affects one Type only, so we only need Task Type.

Schema (from our Project Management Template):

Data:

Formula text ( Formula field must be created on the Task level):

ToDays(Planned.End - Planned.Start) - how many days did we plan to spend

ToDays(Actual.End - Actual.Start) - how many days have we really spent

Formula explanation:

Notes:

  • If you have an existing formula (like the one used above), and then change a field name, Fibery will automatically update your formula to reflect the new field name.

Pro users tips πŸ’ͺ:

Too many formulas make Views cluttered. So, if we want to check the difference between Actual and Planned Durations, we can input that into one formula field. The syntax will look like this.

(ToDays(Planned.End - Planned.Start) - ToDays(Actual.End - Actual.Start))

And it will be also helpful to know how many days have we spent on the Project Level - let's summarize all the Tasks duration.

The syntax will look like this, and the Formula will be created on the Project Level.

Tasks.Sum(ToDays(Planned.End - Planned.Start)) - how many days did we plan to spend

Tasks.Sum(ToDays(Actual.End - Actual.Start)) - how many days have we really spent

And we can even see the difference between Planned and Actual on the Project Level. Here is how the formula will look like:

Tasks.Sum((ToDays(Planned.End - Planned.Start) - ToDays(Actual.End - Actual.Start)))

Logic (If, and, or)

Difficulty level: ⭐⭐⭐⭐

Function

Formula syntax

If

Returns some results based on condition. Switches between two options based on another value.
- mark urgent tasks based on deadline coming
- check whether your employee has too many tasks
- cycle time

If(Actual.End < (Today()-Days(3)), "Urgent","Ok")

If(Tasks.Count() > 10, "Overwork", "Ok")

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

Or

Use it, when you want to add some conditions, comply with any of which will work for you. The number of conditions is almost unlimited.
- you want to mark Deal Done, if it was Won or Lost, and cherish Hopes in other cases

If(State.Name = "Won" or State.Name = "Lost", "Done","Hope")

And

Use it, when you want to add some conditions, comply with all of which will work for you. The number of conditions is almost unlimited.
- if your Task wasn't finished till today as planned, then it is called Missed, if it was, then Finished.
- rank your Task based on several conditions

If(Actual.End > Today() and State.Final != true, "Finished","Missed")

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

Now, let's create If Formula step-by-step πŸ’ͺ:
We will check the Status of our Sprint - whether it is a Past, Current, or Future one - based on its period. This Formula already lives in our Software Management template, but it is better to understand it, not only use 🧐

Schema (from our Software Development Template):

Data:

Formula text ( Formula field must be created on the Sprint level):

If(Dates.End < Today(),"Past",If((Dates.Start <= Today()) and (Dates.End >= Today()),"Current","Future"))

Its looks very long, so let's cut it into two parts
If(Dates.End < Today(),"Past" - this is smth that happens if the End Date is less than Today
If((Dates.Start <= Today()) and (Dates.End >= Today()),"Current","Future") - and this is what happens if it is not

Formula explanation:

If formula consists of three parts - Condition + (coma) + what happens if Condition is met + (coma) + what happens if Condition is not met - If(condition, true result, false result)

But here comes the tricky part - you can set an If formula inside the If formula. Lets have a look at our Formula again.

And go a bit deeper

And how do we read this formula?

If the End Date of our Sprint is before Today, then we say, that this Sprint is Past. If the End day of this Sprint is not before Today, then we have to check - whether the Start Date of our Sprint is Today or before and the End day is Today or after, then this Sprint is Current. If Sprint is neither Past, nor Current, then it is a Future one.

So-so joke as a bonus:

If(You understood the explanation, "🌞", you are still "🌞" but explanation is "πŸ’©")

Ok, let's check one more example to be sure, we understood how it works πŸ’ͺ

At first, formulate the expected result in your head as usual - in text. Like "Ok, I think, that this Task is Urgent if it has high Priority and the deadline is coming. Deadline is coming means that is it less than 2 days before the end". And then just convert your thoughts into Formula step by step.

  • If it has high Priority - If(Priority.Name = "High")

  • and the deadline is coming. Deadline is coming means that is it less than 2 days before the end - If(Priority.Name = "High" and Planned.End < (Today() - Days(2))

  • Then I think, that this Task is Urgent - If(Priority.Name = "High" and Planned.End < (Today() - Days(2), "Urgent")

  • If this Priority is not high or a Deadline is not coming, then this Task is not Urgent - If(Priority.Name = "High" and Planned.End < (Today() - Days(2), "Urgent", "Not Urgent")

Notes:

  • If you have an existing formula (like the one used above), and then change a field name, Fibery will automatically update your formula to reflect the new field name.

  • Please, check that your Type has a field, you are going to work with 🧐

  • In this formula, we are writing a lot of words in quotes ("Urgent". "Past", "Current")- by quotation marks we denote Names.

  • Your True/False result doesn't have to be Text. It can be actions - formulas - Sum, Avg, Min, Max, etc.

Common Fibery Errors - what does it mean

  1. Reference to undefined field
    This Error means, that field, you are going to operate with doesn't exist. Maybe you've forgotten to create it, or maybe just made a syntax mistake.
    Here I'm asking Fibery to summarize all the Points, Bugs have. But I haven't created such a field, this is why it will not work.

2. The Count function does not accept any arguments
This Error means, that smth wrong happens with the Count function. We don't mention what are we going to Count. We are Counting the number of Entities only, this is why there should be nothing in brackets.

3. Missing closing parenthesis or comma while parsing argument list for undefined
Well, that means, that you've just forgotten to put a punctuation mark somewhere, and for Formulas that is crucial. The only thing that can help here - your attention. For now, Fibery formulas are not smart enough to show you where is smth missed πŸ˜₯

Did this answer your question?