Formula Engine
The Formula Engine powers computed columns and KPI formulas in ProBeya. It allows you to define expressions that automatically calculate values from other columns or KPI references, eliminating manual data entry and reducing errors.
Formulas follow a syntax similar to spreadsheet applications (Excel, Google Sheets) and support 28 built-in functions across four categories: logic, math, string, and date operations.
Syntax Overview
A formula is a text expression that combines:
- Numeric literals — integers (
42) and decimals (3.14) - Arithmetic operators —
+,-,*,/with standard precedence - Parentheses —
( )for explicit grouping - KPI references —
{KPI:Name}to pull values from other KPIs - Column references —
{Column:Name}to reference other column values - Function calls —
FUNCTION_NAME(arg1, arg2, ...)
Operator Precedence
Operators follow standard mathematical precedence:
- Parentheses
( )— highest precedence - Multiplication and Division
*,/— evaluated left to right - Addition and Subtraction
+,-— evaluated left to right
10 + 20 * 3 = 70 (not 90)
(10 + 20) * 3 = 90 (parentheses override)
100 / 5 / 2 = 10 (left to right)
KPI References
Use the {KPI:Name} syntax to reference the current value of another KPI on the same board. The name must match exactly (case-sensitive).
{KPI:Good Units} / {KPI:Total Units} * 100
If the referenced KPI has no value or does not exist, the formula returns N/A rather than crashing.
Use extractKpiReferences() in the API to discover which KPIs a formula depends on. The system automatically recalculates computed KPIs when any referenced KPI receives a new value.
Built-in Functions
ProBeya's formula engine includes 28 built-in functions organized into four categories.
Logic Functions
| Function | Syntax | Description |
|---|---|---|
| IF | IF(condition, then_value, else_value) | Returns then_value if condition is truthy, otherwise else_value |
| AND | AND(value1, value2, ...) | Returns true if all arguments are truthy |
| OR | OR(value1, value2, ...) | Returns true if any argument is truthy |
| NOT | NOT(value) | Logical negation |
| SWITCH | SWITCH(expr, case1, val1, case2, val2, ..., default?) | Matches expression against cases, returns the paired value |
| COALESCE | COALESCE(value1, value2, ...) | Returns the first non-null argument |
Examples
IF({KPI:OEE} >= 85, "On Target", "Below Target")
SWITCH({KPI:Status}, "green", 100, "amber", 50, "red", 0, -1)
COALESCE({KPI:Primary Source}, {KPI:Backup Source}, 0)
Math Functions
| Function | Syntax | Description |
|---|---|---|
| SUM | SUM(value1, value2, ...) | Sum of all arguments |
| AVERAGE | AVERAGE(value1, value2, ...) | Arithmetic mean |
| MIN | MIN(value1, value2, ...) | Smallest argument |
| MAX | MAX(value1, value2, ...) | Largest argument |
| COUNT | COUNT(value1, value2, ...) | Number of non-null arguments |
| ABS | ABS(value) | Absolute value |
| ROUND | ROUND(value, decimals?) | Round to N decimal places (default: 0) |
| FLOOR | FLOOR(value) | Round down to nearest integer |
| CEIL | CEIL(value) | Round up to nearest integer |
| MOD | MOD(value, divisor) | Modulo (remainder after division) |
Examples
SUM({KPI:Line 1 Output}, {KPI:Line 2 Output}, {KPI:Line 3 Output})
AVERAGE({KPI:Week 1}, {KPI:Week 2}, {KPI:Week 3}, {KPI:Week 4})
ROUND({KPI:OEE} * 100, 1)
MOD({KPI:Batch Number}, 10)
String Functions
| Function | Syntax | Description |
|---|---|---|
| CONCAT | CONCAT(value1, value2, ...) | Concatenate values into a single string |
| LEFT | LEFT(text, count) | Extract the first N characters |
| RIGHT | RIGHT(text, count) | Extract the last N characters |
| MID | MID(text, start, count) | Extract a substring (1-based start position) |
| LEN | LEN(text) | Length of the string |
| UPPER | UPPER(text) | Convert to uppercase |
| LOWER | LOWER(text) | Convert to lowercase |
| TRIM | TRIM(text) | Remove leading and trailing whitespace |
Examples
CONCAT({KPI:Site Code}, "-", {KPI:Line ID})
UPPER(LEFT({KPI:Batch ID}, 3))
LEN(TRIM({KPI:Comments}))
Date Functions
| Function | Syntax | Description |
|---|---|---|
| TODAY | TODAY() | Current date as ISO string (YYYY-MM-DD) |
| DAYS_BETWEEN | DAYS_BETWEEN(date1, date2) | Number of days between two dates |
| FORMAT_DATE | FORMAT_DATE(date, pattern) | Format a date using a pattern string |
| ADD_DAYS | ADD_DAYS(date, days) | Add or subtract days from a date |
Date Pattern Tokens
| Token | Output |
|---|---|
YYYY | Full year (e.g., 2026) |
MM | Month, zero-padded (e.g., 03) |
DD | Day, zero-padded (e.g., 15) |
HH | Hours, zero-padded (24h) |
mm | Minutes, zero-padded |
ss | Seconds, zero-padded |
Examples
DAYS_BETWEEN({KPI:Start Date}, TODAY())
FORMAT_DATE(ADD_DAYS(TODAY(), 7), "YYYY-MM-DD")
IF(DAYS_BETWEEN(TODAY(), {KPI:Due Date}) < 0, "OVERDUE", "ON TRACK")
Error Codes
When a formula cannot be evaluated, it displays an error code in the cell — similar to Excel error indicators. Each error code identifies the specific failure type.
| Error Code | Display | Description |
|---|---|---|
REF | #REF! | Referenced column does not exist |
CIRCULAR_REF | #CIRCULAR_REF! | Circular dependency detected between formula columns |
DIV_ZERO | #DIV/0! | Division by zero attempted |
TYPE | #TYPE! | Incompatible types for the operation (e.g., string * number) |
SYNTAX | #SYNTAX! | Invalid formula syntax (parse error) |
NULL | #NULL! | Referenced value is null or undefined |
OVERFLOW | #OVERFLOW! | Numeric result exceeds safe integer range |
When a formula references a KPI that has no value, the formula returns null (displayed as "N/A" in the UI) rather than throwing an error. This ensures that missing data never crashes the KPI dashboard — it simply shows a placeholder until data is available.
Troubleshooting Errors
#REF! — Check that the referenced KPI name in {KPI:Name} exactly matches the KPI definition name. Names are case-sensitive.
#CIRCULAR_REF! — KPI "A" references KPI "B", which references KPI "A" (directly or through a chain). Break the cycle by removing one of the references.
#DIV/0! — Use IF to guard against zero denominators:
IF({KPI:Total Units} > 0, {KPI:Good Units} / {KPI:Total Units} * 100, 0)
#SYNTAX! — Check for mismatched parentheses, missing operators between values, or unsupported characters.
Validation
The formula editor validates syntax in real time as you type. A red border on the formula input field indicates a syntax error, while a green border confirms the formula is structurally valid.
Validation checks:
- All parentheses are properly matched
- All function names are recognized
- All KPI references follow the
{KPI:Name}format - Operators are placed correctly (no consecutive operators like
+ +)
Validation confirms that the formula is syntactically correct, but it does not check whether referenced KPIs exist or have values. A formula can be valid syntactically but still return #REF! at evaluation time if a referenced KPI is deleted.
Computed KPIs
When a formula is assigned to a KPI definition, the KPI becomes a computed KPI:
- Manual data entry is disabled for computed KPIs
- The value is automatically recalculated whenever a referenced KPI receives a new value
- The data source is displayed as "formula" in the audit trail
- Computed KPIs participate in the same traffic-light threshold system as manually entered KPIs
Creating a Computed KPI
- Open a board and navigate to the KPIs tab.
- Click + Add KPI.
- Fill in the standard definition fields (name, unit, category, etc.).
- In the Data Source dropdown, select
formula. - Enter the formula expression in the formula editor.
- Click Validate to check syntax.
- Click Save. The KPI value is computed immediately.
Permissions
| Action | Required Role |
|---|---|
| View formula results | Any board member |
| Create/edit formula columns | Board admin or workspace admin |
| Create/edit computed KPIs | Board admin or workspace admin |
Related Features
- KPI Boards — Computed KPIs use formulas for automatic value calculation
- Action Log — Formula-driven KPI thresholds can trigger action creation
- CSV Import — Imported data is protected against formula injection