Calculated Custom Fields

In this article:

1. What is a Calculated Custom Field?
2. How to Create a Calculated Custom Field?
    2.1. Calculated Custom Fields Limitations
3. Types of Calculated Custom Fields
3.1. Calculated Number Fields
3.2. Calculated Date Fields
4. Practical Examples of Using Calculated Custom Fields

 

1. What is a Calculated Custom Field?

The “Calculated custom fields (CCF)” is a key functionality that aims to expand the automation capabilities of Businessmap, especially around data generation and calculation.

Calculated fields allow you to dynamically populate values and create flexible fields inside the platform. With CCFs, you are able to combine multiple custom fields together and use functions to automate the population of values inside a specific field.

2. How to Create a Calculated Custom Field?

Calculated Custom Fields are created and managed at a global Account level by users with relevant permissions. To create custom fields, go to the Administration menu and click "Custom fields" under Card Management. The system redirects you to the dedicated panel. Click the "New Custom Field" link on the top right side of the panel.

Enter the custom field name and select a dedicated color that will distinguish the new custom field.
Select the type of your custom field from the drop-down - either Calculated number or Calculated date:

2.1. Calculated Custom Fields Limitations

There is a limit of 10 calculated custom fields you can use per board. This includes both calculated custom fields of type date and number.

 

3. Types of Calculated Custom Fields

Currently, two types of Calculated custom fields exist: Calculated number and Calculated date.

3.1. Calculated Number Fields:

Calculated number fields allow you to build formulas by using other custom fields of type “Number” and “Dropdown,” the "Card Size" field, standard fields of type “Date” (e.g. Created at)*, or standard numeric values of your choice.

* - applicable for DATE_DIFF functions only.

To add dynamic custom fields or a function, all you have to do is type the “@” symbol:

 

All functions available when building a calculated number field are the following:

• AVERAGE - the formula calculates the average value from a set of values.
• CEILING – It rounds your value up to the nearest integer. If “value 1” is 1.2, it will round it up to the nearest integer, which is “2”.
• FLOOR – It rounds your value down to the nearest integer. In this case, if “value 1” is 1.2, it will round it down to the nearest integer which is “1”.
• IF_EMPTY – The formula will return the “fallback_value” if “value 1” is empty. If not - it will return “value 1”
• IF_ZERO – The formula will return the “fallback_value”, if “value 1” is zero. If not - it will return “value 1”
• LOG – The formula represents a logarithmic expression that returns the power to which “base” is raised to get “value 1”.
• MAX – It returns the maximum value from a given set of values.
• MIN – the formula returns the minimum value from a given set of values.
• POWER – It returns “value 1” raised to a given power (“base”).
• ROUND – It returns “value 1” rounded to a specific number of decimal places.
• DATE_DIFF - This formula returns the difference between two dates, where the result is a number. The first parameter in the function represents "to date", while the second one is the "from date" value. The result would be either a positive (when "to date" > "from date") or a negative number (when "from date" > "to date"). The standard date fields “Card Deadline,” “Created at,” “Card planned start date,” “Card planned end date,” “Card actual start date,” and “Card actual end date” can also be included in the function.

All available formulas are, as follows:

• DATE_DIFF_YEARS
• DATE_DIFF_MONTHS
• DATE_DIFF_DAYS
• DATE_DIFF_HOURS
• DATE_DIFF_MINUTES
• DATE_DIFF_SECONDS

These six formulas return the number of the whole period between the dates, e.g. date_diff_days between 2023-07-12 15:00:00 (first date) and 2023-07-14 16:00:00 (second date) would be 2, but between 2023-07-12 15:00:00 and 2023-07-14 14:00:00 would be 1 (as there has been only one whole day).

• DATE_DIFF_WORKING_DAYS - the behavior of this formula would be different, as the exact hour is not taken into consideration. For instance, if the standard working week is Monday to Friday, then date_diff_working_days would be:

between Thursday and Friday - 1,

between Friday and Saturday - 0,

between Saturday and Sunday - 0,

between Sunday and Monday - again 0,

between Friday and Monday - 1,

between Sunday and the following Saturday - 5.

Note: Since the date fields hold hours and these hours are used when setting dates, users within different timezones could be seeing different values/results of the calculation. To make sure all users see the same value when calculating the date_diff_working_days formula, make sure that both dates are a result of a previously set SET_TIMEZONE function.

For instance, if you would like to take the difference between two working days - 2023-07-24 12:00:00 Europe/Sofia (Monday) and 2023-07-26 02:59:59 Europe/Sofia (Wednesday) and you only use the DATE_DIFF_WORKING_DAYS (CF2 - the first working day, CF1 - the second working day) without setting up the timezone, the result would be 1 since the system would calculate 2023-07-24 09:00:00 (UTC) and 2023-07-25 23:59:59 (UTC).

To ensure the calculation would bring 2 as an expected result, the Timezone should be included in the formula. Following the example above, DATE_DIFF_WORKING_DAYS (SET_TIMEZONE(CF2,' EUROPE/SOFIA'), CF1) would bring 2 as a result, due to the fact that the SET_TIMEZONE function is “instructing” the system to recalculate according to the timezone needed: the dates are already 2023-07-24 09:00:00 and 2023-07-26 02:59:59, and the result is 2.

 

Important clarification about using "Card planned start/end date" in DATE_DIFF functions

Currently, the Card planned start and Card planned end fields do not support exact times, only dates. The system assigns them a default time of 12:00:00 (p.m.) UTC time that cannot be edited. So, if you are using the DATE_DIFF_HOURS function, for example, and you have a planned start date of 2023-11-14 and a datetime custom field for 2023-11-13 12:00:00, the result will be 24.

 

Important clarifications about using custom fields of type “Dropdown” in calculated formulas

• When selecting a dropdown field for building a formula, you'll see several options for extracting data from the dropdown field. You can extract the sum, min, max, average, first, and last of the selected values, including the “Other value” data if it is allowed in the dropdown field configuration.

• By default, the system will extract the first number from left to right, but you can change that by enabling the “Search for numbers in the field value from right to left” option. If you do that, you will see an arrow next to the dropdown function in the formula (see image below). For example, if you have a dropdown value of “5 B 8” and the option is enabled, the system will extract 8. Otherwise, it will extract 5.

 

3.2. Calculated Date Fields:

The calculated date fields allow you to build formulas using other custom fields of type “Date” including the standard fields “Card Deadline,” “Created at,” “Card planned start date,” “Card planned end date,” “Card actual start date,” and “Card actual end date,” as well as custom fields of type number* or standard numeric values* of your choice.

* - where applicable

Again, by typing the “@” symbol, you can choose what functions and custom fields to use and dynamically build your calculated date field:

All functions available for the Calculated date fields are the following:

• MAKE_DATE - allows you to build your custom date based on the following parameters: (year, month, day, hour, minute, second, timezone);
  Example timezone - ‘Europe/London’
• MAX - returns the latest date in a set of dates.
• MIN - returns the earliest date in a set of dates.
• IF_EMPTY - returns “date” if the date is not empty. Otherwise, it will return the “fallback_date”.

We can group the rest of the functions into the following categories:

• ADD - These functions return a specific date/time expression with a given number of:

1. ADD_DAYS - Days
2. ADD_HOURS - Hours
3. ADD_MINUTES - Minutes
4. ADD_MONTHS - Months
5. ADD_SECONDS - Seconds
6. ADD_WORKING_DAYS - Working days
7. ADD_YEARS - Years

• SUBTRACT - this formula returns a specific date/time expression with a given number of:

1. SUBTRACT_DAYS - Days
2. SUBTRACT_HOURS - Hours
3. SUBTRACT_MINUTES - Minutes
4. SUBTRACT_MONTHS - Months
5. SUBTRACT_SECONDS - Seconds
6. SUBTRACT_WORKING_DAYS - Working days
7. SUBTRACT_YEARS - Years

• END - you can get the:

1. END_OF_DAY - last hour from the current day
2. END_OF_MONTH - the last date from the current month
3. END_OF_WEEK_FIRST_DAY_MONDAY - the last date from a Mon-Sun week 
4. END_OF_WEEK_FIRST_DAY_SUNDAY - the last date from a Sun-Sat week
5. END_OF_YEAR - the last date from the current year.

• START - On the contrary, these functions return the:

1. START_OF_DAY - the first hour from the current day
2. START_OF_MONTH - the first date from the current month
3. START_OF_WEEK_FIRST_DAY_MONDAY - the first date from the current (Mon-Sun) week
4. START_OF_WEEK_FIRST_DAY_SUNDAY - the first date from the current (Sun-Sat) week 
5. START_OF_YEAR - the first date from the current year

• SET - These functions help you set a specific date/time expression with custom units such as years, months, days, hours, minutes, and seconds. With the SET_TIMEZONE function, you can adjust a date/time expression to a given timezone:
• SET_DATE
• SET_DAY
• SET_HOUR
• SET_MINUTE
• SET_MONTH
• SET_SECOND
•  SET_TIME
•  SET_TIMEZONE
•  SET_YEAR

 

4. Practical Examples of Using Calculated Custom Fields

Calculated Number Fields Examples

AVERAGE

With the AVERAGE function, you can get the average value of several custom fields, each representing a different budget estimation for a new project. This can aid in budget and resource planning.

 

CEILING

You can use the CEILING function to calculate expenses. For example, you can round expenses up to the nearest counting number.

 

FLOOR

Similarly, you can use the FLOOR function to round values down to the nearest integer.

 

IF_EMPTY

You can use the IF_EMPTY function in budgeting. Let’s say you have a custom field (e.g. “Budget Allocation”) to record the allocated budget to each task or project. However, not every task may have a specified budget, and you want to ensure that there is always a numerical value representing budget allocation. With the IF_EMPTY function, you can set a default budget value so even if the field is empty, each item has a budget allocation.

 

IF_ZERO

The IF_ZERO function can be very useful for custom fields that are used in calculations as it lets you provide a fallback value to ensure the calculation goes through.

 

LOG

If your team is estimating tasks/projects with a wide range of numbers within the card size field (e.g. between 10 and 100), you can use the LOG function to transform this scale into a smaller, more "reasonable" range → for example, LOG(Card size,2).

 

MAX

With the MAX function, you can automatically track the biggest value from a set of values from various custom fields (e.g. costs, budgets, etc.). 

 

MIN

The MIN function can be very useful in revenue tracking. Through it, you can see the minimum profit for a set of projects, which can help with budget planning and revenue projections.

 

POWER

If your team uses a custom field (of type number) to define the complexity of a task (e.g. numbers between 1 and 5, where 1 is a very simple task and 5 is a quite complex one), you can use the POWER function to “transform” this complexity into a number-of-work-days estimation for completing the task, where each level of complexity is doubled to get the days needed for the task to be completed.

 

ROUND

You can use the ROUND function to simplify calculations (e.g. for budgeting, costs, revenue, etc.) by rounding them to the second decimal place.

 

DATE_DIFF

A common example of using the DATE_DIFF functions is to calculate the duration of an initiative based on its planned start and end dates. This is what the setup would look like:

You can also use the DATE_DIFF functions to calculate delivery times. In a software development scenario, you can use this function to track the difference between two dates — when you got a client request and when it was delivered to the customer. With the DATE_DIFF_WORKING_DAYS, the function setup can look like this:

Examples Without Functions

Certain use cases for calculated custom fields of type number can be performed with simple calculations. For example, if you want to find the difference between two other custom fields (e.g. “Revenue” minus “Costs” to calculate the profit), you can do it with a simple “A - B” calculation, where “A” is the first custom fields and “B” is the second. The custom fields you use in the calculation must be of type number.

The setup would look like this:

These types of calculations work for the following symbols: “-” (for subtraction), “+” (for addition), “*” (for multiplication), and “/” (for division).

 

Calculated Date Fields Examples

MAKE_DATE

The MAKE_DATE function can be very useful when working with international teams across different timezones. You can use it to set and track milestone dates that consider each team’s local time and keep everyone on the same page about deadlines. For example, you can create a custom milestone date (by default, it will be set in the UTC timezone) that will automatically be converted to the timezone of each user.

 

MAX

In a manufacturing scenario, you can use the MAX function to automatically determine the latest expected delivery date among a set of supplies/materials. This will help you monitor the project's progress and identify potential delays in deliveries that might affect overall production.

 

MIN

A common example of using the MIN function is to find the earliest deadline among a group of cards/tasks to help you prioritize and allocate resources efficiently.

 

IF_EMPTY

Let’s say you are working on a software development project and have a custom field “Code Freeze Date” that marks the date when all coding activities should be completed for a specific feature or release. In case a code-free date is not specified, you can use the IF_EMPTY function to set a fallback date to avoid project delays.

 

ADD

With the ADD function, you can set more accurate project delivery dates. If you know when a project is set to start (using a custom field of type date), you can use the ADD_WORKING_DAYS function to automatically calculate the delivery date depending on your working days. The function setup can look like this:

Note: Instead of a custom field for the start date, you can also use the “Card created at” standard field if you want to calculate the delivery date from the moment a card is created.

 

SUBTRACT

A possible scenario of using the SUBTRACT_WORKING_DAYS function is to calculate a project's start date if you know its due date. You can set up the formula so it takes the card's deadline and subtracts the working days necessary to complete the task.

 

END

If you are a PM and want to set monthly review meetings to discuss progress, issues, and planning for the next month, you can use the END_OF_MONTH function to automatically calculate and schedule project review meetings on the last day of each month.

 

START

If you want to ensure that certain project activities always start at the beginning of each month (e.g. status updates, budget reviews, etc.), you can use the START_OF_MONTH function to automatically set the first day of each month for various project activities, ensuring that they align with your monthly planning cycle.

 

SET

If you are working with teams spread throughout the globe, you can use the SET_TIMEZONE function to set a single timezone for a task/project. This will make it easier to track progress and deadlines and avoid discrepancies caused by differing timezones.

 

Notes:

1. Both the calculated number and date fields can not be edited manually on a card level.
   
2. Users with eligible permissions can lock calculated number or date fields' properties on a global level which makes them not editable on a board level.
3. It’s not possible to use a custom date inside the calculated date field unless you build it with any Calculated Custom Field Date function.
4. If in the result of a given formula, there is a missing value, that means that the number/date is not valid (e.g. a date is way behind in the past/ahead in the future or a number is exceeding a certain limit).
5. Within the Calculated Custom Field Date, all functions return a date expression as a result (i.e. in each formula, that requires a date expression, that expression could be replaced with another formula). The main functions that are able to create date expressions alone (without using another function) are, as follows:

• make_date
• card.deadline
• card.created_at
• card.custom_field_date