Custom Formulas are an ever evolving feature in Acuite. Currently, formulas can only be used in a handful of places - typically in our custom dashboard tables. They are powerful tools to allow you to add custom columns and fields based on your own formulas, however at present they are not user-friendly to work with. The end goal will be to have a formula builder within Acuite, but for now, feel free to get in touch with our support team if you'd like help in making use of this feature.
Custom formulas are defined as a piece of text containing one or more 'tokens'. Each token represents a word, phrase, function or operator. These formulas are designed to mimic what you might see in an Excel formula. Typically some of these tokens will represent fields - these fields are replaced with actual values when the formula is evaluated. Operators and functions can apply special logic to those values.
A simple formula might be something like this:
[Forecast Contract Value] - [Forecast Final Cost]
This will be evaluated with the actual figures for value & cost, to give you a Forecast Margin result.
A more advanced formula could look like this:
datediff([Actual Start Date], today(), "d") / datediff([Actual Start Date], [Forecast Completion Date], "d") * 100
This calculates the number of days elapsed in the project, divided by the total number of days in the project duration, giving you a % Complete by Time result.
Below is a brief reference guide for what is supported by our formula system.
Math
Supported operations: + - / *
Default order of operations is multiplication/division before addition/subtraction
Parentheses can be used to change the order of operation, e.g. (5 + 2) * 3
Dynamic Fields
Any text in square brackets [] will be treated as a field name
We will look up that field for the chosen data type and insert the value when the formula is evaluated
E.g. [Forecast Final Cost] - [Forecast Contract Value]
Dates and Literal Text
Any text surrounded by double quotes will first be checked to see if it is a date, and will then be treated as literal text
E.g. "17/05/2024" will be treated as a date, and can be used in functions that work with dates
E.g. "Consent" will be treated as text
Data Types
In general, custom formulas can only work with a single data type. For example, a custom formula attached to a Cost record, can only work with Cost fields. A custom formula attached to a custom table can only work with columns that are visible in that table.
We are slowly adding support for formulas that can reference other data types. For now, this is limited to Cost custom formulas that can reference Cost Detail. These formulas can sum values from our cost detail snapshots, and include them in Cost custom formulas
For example:
Final Cost=[Cost to Date] + [Cost to Complete] + [CostDetail::Adjustments]This will add the Cost fields
Cost to DateandCost to Completeas well as the sum of all rows in the Cost Detail table for theAdjustmentscolumnFor now, this only supports Cost Detail snapshots of type 'CombinedBudget' (Standard budget data and/or cost forecasts). It does not yet support Procurement or Revenue Forecast data.
Logic Expressions
Supported operations: <, <=, =, !=, >=, >
<, <=, >, >= support numbers or dates only
=, != support numbers, dates, and text
Intended for use with logic functions
Logic Functions
IF(expression, value_when_true, value_when_false)
If the expression evaluates to true, a non-zero number, or a non-empty string, the output will be the second argument.
If the expression evaluates to false, zero, or an empty string, the output will be the third argument.
IF0(expression, value_when_zero, value_when_nonzero)
Shortcut for IF(expression = 0, value1, value2)
IFNULL(expression, value_when_null, value_when_not_null)
If the expression evaluates to null (e.g. if a field is missing or has no value), the first value will be returned. If the expression returns a value (including zero), the second value will be returned.
Note that this can be chained with other expressions
Example:
IFNULL([Field A], 0, [Field A] - [Field B])
Math Functions
abs(value)
Outputs the absolute value of a number
E.g. abs(-5) = 5
String Functions
Start(expression, length)
Outputs the first length characters from the expression. Works with any data type.
E.g. Start("Text", 3) = Tex
End(expression, length)
Outputs the last length characters from the expression. Works with any data type.
E.g. End("Text", 3) = ext
Mid(expression, start, length)
Outputs length characters from the expression, starting at start. Indexes start at 1 (e.g. the first character is position 1).
E.g. Mid("Text", 2, 2) = ex
Date Functions
datediff(startdate, enddate, "part")
Calculates the difference between two dates
part can be one of:
d (calculated in calendar days)
w (weeks)
m (months)
y (years)
Currently only complete periods are counted, and the function returns whole numbers
E.g. datediff("01/01/2024", "02/01/2024", "d") = 1
dateduration(startdate, enddate, "part")
Equivalent to datediff() except this count is inclusive of the end date
The intention is for task durations where you count from the beginning of the first day to the end of the last day
E.g. dateduration("01/01/2024", "02/01/2024", "d") = 2
today()
Returns today's date
Intended to be used in conjunction with other date functions
Chaining
Any expression part can be another expression, allowing you to chain formulas together
Examples
[Actual Cost to Date] / [Forecast Final Cost] * 100Cost formula: % Complete by Cost
If(Mid([Number], 2, 1) = 4, "Boost", "Consent")Project formula: If the 2nd digit of the Project Number is a 4, display Boost otherwise display Consent
datediff([Actual Start Date], [Forecast Completion Date], "m")Count the number of months in a project
datediff([Actual Start Date], today(), "d")Count the number of days since project start
ifnull([Field A], 0, [Field A] - [Field B])If Field A is missing or has no value, return zero. Otherwise return the calculated value.
[Cost to Date] + [Cost to Complete] + [CostDetail::Adjustments]Add the Cost fields to the sum of the Adjustments column from our cost detail snapshots.