Content:
1. What is a Calculated Custom Field?
2. How to Create a Calculated Custom Field?
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 Kanbanize, 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:
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 field allows you to build formulas by using other custom fields of type number, the "Card Size" field, or standard numeric values of your choice. 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.
- CARD.CUSTOM_FIELD_NUMBER (field id)
- 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").
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) и 2023-07-14 16:00:00 (second date) would be 2, but between 2023-07-12 15:00:00 и 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 time zones 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 time zone, 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 Time Zone 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 time zone needed: the dates are already 2023-07-24 09:00:00 and 2023-07-26 02:59:59, and the result is 2.
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” and “Created at”, 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 data 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
- CARD.CREATED_AT ()
- CARD.CUSTOM_FIELD_DATE (field id)
- 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
A practical example of using Calculated Number fields in your process would be, for instance, getting the average value of several custom fields, each representing a different budget estimation for a new project (using the “Average” function).
Following another example, 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 could 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.
When it comes to calculated date fields a possible scenario would be setting up a deadline in a certain number of working days, instead of calendar ones, using the ADD_WORKING_DAYS function, etc.
Notes:
- Both the calculated number and date fields can not be edited manually on a card level.
- 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.
- 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.
- 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).
- 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