> ## Documentation Index
> Fetch the complete documentation index at: https://developer.kodexa.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Formulas Guide
> Write Kodexa formulas for calculations, string manipulation, dates, and logical decisions, referencing taxonomy attributes and conditionally formatting data.
Formulas in Kodexa allow you to perform calculations, manipulate strings, work with dates, and make logical decisions. They can reference attributes from your taxonomy, perform complex calculations, and conditionally format or validate data.
## Basic Syntax
### Simple Expressions
```typescript theme={null}
// Basic arithmetic
3 + 4 // Returns: 7
10 - 3 // Returns: 7
4 * 5 // Returns: 20
15 / 3 // Returns: 5
```
### Attribute References
Reference attributes from your data objects using curly braces:
```typescript theme={null}
{attributeName} // Single attribute value
{IS_OT_RVNU_AMT} // Specific attribute by name
{ClaimOverview/ClaimID} // Hierarchical path to attribute
```
### Parent Navigation
Access parent data object attributes using `../`:
```typescript theme={null}
{../division_name} // Access immediate parent
{../../grandparent_id} // Access grandparent (two levels up)
{../division_budget} / 4 // Use parent attribute in calculation
```
### Attribute Expressions (Filtering)
Filter attribute values using property expressions:
```typescript theme={null}
{CDAD_IS_Revenue} // All values
{CDAD_IS_Revenue}(@cell_index="0") // First cell only
{CDAD_IS_Revenue}(@cell_index="1") // Second cell only
```
## Comparison Operators
```typescript theme={null}
// Equality
3 = 3 // true
3 = 4 // false
3 != 4 // true
3 != 3 // false
// Comparison
3 < 4 // true
3 <= 3 // true
3 > 2 // true
3 >= 3 // true
```
## Logical Operators
```typescript theme={null}
// AND operator
3 > 2 && 4 > 3 // true
3 > 2 && 4 < 3 // false
// OR operator
3 > 2 || 4 < 3 // true
3 < 2 || 4 < 3 // false
// NOT operator
!(3 < 2 || 4 < 3) // true
// Multiple conditions
3 > 2 || 4 > 3 || 5 > 4 // true
3 > 2 && 4 > 3 && 5 > 4 // true
```
## Mathematical Functions
### sum
Add multiple values together.
```typescript theme={null}
sum(3, 4) // Returns: 7
sum(3, 4, 5) // Returns: 12
sum(3, 4, 5, 6) // Returns: 18
sum() // Returns: 0
sum({billcharge/chargeamount}) // Sum attribute values
```
### sumabs
Sum of absolute values.
```typescript theme={null}
sumabs(-1, 2, -3) // Returns: 6
sumabs() // Returns: 0
sumabs({deviation1}, {deviation2}, {deviation3}) // Sum absolute deviations
```
### average
Calculate the mean of values.
```typescript theme={null}
average(3, 4) // Returns: 3.5
average(3, 4, 5) // Returns: 4
average(3, 4, 5, 6) // Returns: 4.5
average() // Returns: NaN
```
### max
Find the maximum value.
```typescript theme={null}
max(3, 4) // Returns: 4
max(3, 4, 5) // Returns: 5
max(3, 4, 5, 6) // Returns: 6
max() // Returns: 0
```
### min
Find the minimum value.
```typescript theme={null}
min(3, 4) // Returns: 3
min(3, 4, 5) // Returns: 3
min(3, 4, 5, 6) // Returns: 3
min() // Returns: 0
```
### count
Count the number of items.
```typescript theme={null}
count(1, 2, 3, 4) // Returns: 4
count({ClaimOverview/Claimdetails/ClaimID}) // Count attribute occurrences
```
### abs
Get absolute value (remove negative sign).
```typescript theme={null}
abs(-1) // Returns: 1
abs(5) // Returns: 5
```
### ceil
Round up to nearest integer.
```typescript theme={null}
ceil(1.5) // Returns: 2
ceil(1.1) // Returns: 2
```
### floor
Round down to nearest integer.
```typescript theme={null}
floor(1.8) // Returns: 1
floor(1.9) // Returns: 1
```
### round
Round to nearest integer.
```typescript theme={null}
round(1.5) // Returns: 2
round(1.4) // Returns: 1
```
### decimalplaces
Round to a specific number of decimal places with optional rounding mode.
```typescript theme={null}
decimalplaces(123.456, 2) // Returns: 123.45
decimalplaces(123.456, 1) // Returns: 123.4
decimalplaces(123.456, 2, "round_up") // Returns: 123.46
decimalplaces(123.456, 2, "round_down") // Returns: 123.45
decimalplaces(-123.456, 2, "round_ceil") // Returns: -123.45
decimalplaces(-123.456, 2, "round_floor") // Returns: -123.46
decimalplaces(123.4567, 2, "round_half_up") // Returns: 123.46
decimalplaces(123.4567, 2, "round_half_even") // Returns: 123.46
```
**Rounding modes:**
* `round_up` - Round away from zero
* `round_down` - Round towards zero
* `round_ceil` - Round towards positive infinity
* `round_floor` - Round towards negative infinity
* `round_half_up` - Round half values up
* `round_half_down` - Round half values down
* `round_half_even` - Banker's rounding (round to nearest even)
* `round_half_ceil` - Round half values towards positive infinity
* `round_half_floor` - Round half values towards negative infinity
### stddeviation
Calculate standard deviation of values.
```typescript theme={null}
stddeviation(1, 2, 3, 4, 5) // Returns: ~1.58
stddeviation(10, 20, 30, 40, 50) // Returns: ~15.81
```
### Empty group references
When an aggregate function receives a group reference that resolves to zero instances — e.g. `sum({Accessorials/ChargeAmount})` against a shipment whose accessorial line items have all been deleted — the reference resolves to an empty list and the function computes accordingly:
| Function | Result on empty list |
| ------------------------- | -------------------- |
| `sum`, `sumabs` | `0` |
| `count` | `0` |
| `max`, `min` | `0` |
| `average`, `stddeviation` | `NaN` |
Formulas that watch a parent and aggregate over a child group therefore re-evaluate to the empty-list result when the last child row is removed, instead of preserving the previous total. The recalculator persists this — a `sum` whose group has just been emptied is rewritten to `0` rather than leaving the prior figure on screen.
This applies only when the final path segment resolves to an empty list. Aggregates over a group that doesn't exist at all (the taxon was never defined) still surface as a missing-reference error in the formula explain panel; aggregates that hit a runtime/syntax error preserve the previous value so transient failures don't blank legitimate output.
## String Functions
### concat
Combine multiple strings into one.
```typescript theme={null}
concat("hello", "world") // Returns: "helloworld"
concat("hello", " ", "world") // Returns: "hello world"
concat({department_name}, " under ", {../division_name}) // Mix attributes and text
concat("Budget: ", {../division_budget}) // Combine text and numbers
```
### uppercase
Convert text to uppercase.
```typescript theme={null}
uppercase("hello") // Returns: ["HELLO"]
uppercase("hello", "world") // Returns: ["HELLO", "WORLD"]
```
### lowercase
Convert text to lowercase.
```typescript theme={null}
lowercase("HELLO") // Returns: ["hello"]
lowercase("HELLO", "WORLD") // Returns: ["hello", "world"]
```
### trim
Remove leading and trailing whitespace.
```typescript theme={null}
trim(" hello ") // Returns: ["hello"]
trim(" text ") // Returns: ["text"]
```
### substring
Extract part of a string.
```typescript theme={null}
substring("hello", 1, 3) // Returns: "el" (start at position 1, length 3)
substring("hello world", 0, 5) // Returns: "hello"
```
### length / strlen
Get the length of a string.
```typescript theme={null}
length("hello") // Returns: 5
strlen("hello") // Returns: 5 (same as LENGTH)
strlen("") // Returns: 0
```
### len
Get the length of a string or count array items.
```typescript theme={null}
// String length
len("hello") // Returns: 5
len("") // Returns: 0
// Array length
len(1, 2, 3) // Returns: 3
len("a", "b", "c", "d") // Returns: 4
len() // Returns: 0
```
### contains
Check if a string contains a substring.
```typescript theme={null}
contains("hello", "ell") // Returns: true
contains("hello", "xyz") // Returns: false
```
### startswith
Check if a string starts with a substring.
```typescript theme={null}
startswith("hello", "he") // Returns: true
startswith("hello", "lo") // Returns: false
```
### endswith
Check if a string ends with a substring.
```typescript theme={null}
endswith("hello", "lo") // Returns: true
endswith("hello", "he") // Returns: false
```
### replace
Replace text within a string.
```typescript theme={null}
replace("hello", "e", "a") // Returns: "hallo"
replace("hello world", "world", "there") // Returns: "hello there"
```
### split
Split a string into an array by delimiter.
```typescript theme={null}
split("hello,world", ",") // Returns: ["hello", "world"]
split("a|b|c", "|") // Returns: ["a", "b", "c"]
```
### regex
Test if a string matches a regular expression pattern.
```typescript theme={null}
// Basic matching
regex("hello", "ell") // Returns: true
regex("hello", "xyz") // Returns: false
// Pattern matching
regex("hello", "^h.*o$") // Returns: true (starts with h, ends with o)
regex("hello", "[aeiou]") // Returns: true (contains vowels)
regex("hello", "d+") // Returns: false (no digits)
// Case sensitivity
regex("Hello", "hello") // Returns: false (case-sensitive)
regex("", ".*") // Returns: true (matches empty)
```
## Date Functions
### datemath
Add or subtract time from a date.
```typescript theme={null}
// Add days
datemath("2023-04-01", "days", 5) // Returns: 2023-04-06
datemath("2023-04-01", "days", -5) // Returns: 2023-03-27
// Add weeks
datemath("2023-04-01", "weeks", 2) // Returns: 2023-04-15
datemath("2023-04-01", "weeks", -2) // Returns: 2023-03-18
// Add months
datemath("2023-04-01", "months", 2) // Returns: 2023-06-01
datemath("2023-04-01", "months", -2) // Returns: 2023-02-01
// Add years
datemath("2023-04-01", "years", 2) // Returns: 2025-04-01
datemath("2023-04-01", "years", -2) // Returns: 2021-04-01
// Special keywords
datemath("today") // Returns: current date
datemath("now") // Returns: current date and time
```
### daysbetween
Calculate days between two dates.
```typescript theme={null}
daysbetween("2023-01-01", "2023-01-10") // Returns: 9
daysbetween("2023-12-25", "2024-01-01") // Returns: 7
```
### weeksbetween
Calculate weeks between two dates.
```typescript theme={null}
weeksbetween("2023-01-01", "2023-01-15") // Returns: 2
weeksbetween("2023-01-01", "2023-02-01") // Returns: ~4
```
### monthsbetween
Calculate months between two dates.
```typescript theme={null}
monthsbetween("2023-01-01", "2023-04-01") // Returns: 3
monthsbetween("2023-01-15", "2023-12-15") // Returns: 11
```
### isbeforedate
Check if first date is before second date (ignores time).
```typescript theme={null}
isbeforedate("2023-12-24", "2023-12-25") // Returns: true
isbeforedate("2023-12-25", "2023-12-24") // Returns: false
isbeforedate("2023-12-25 09:00:00", "2023-12-25 17:30:00") // Returns: false (same day)
isbeforedate("2023-01-31", "2023-02-01") // Returns: true
isbeforedate("2023-12-31", "2024-01-01") // Returns: true
```
### isafterdate
Check if first date is after second date (ignores time).
```typescript theme={null}
isafterdate("2023-12-26", "2023-12-25") // Returns: true
isafterdate("2023-12-25", "2023-12-26") // Returns: false
isafterdate("2023-12-25 09:00:00", "2023-12-25 17:30:00") // Returns: false (same day)
isafterdate("2024-01-01", "2023-12-31") // Returns: true
```
### isdate
Check if a string is a valid date.
```typescript theme={null}
// Valid dates - ISO format
isdate("2023-12-25") // Returns: true
isdate("2023-12-25T10:30:00") // Returns: true
isdate("2023-12-25T10:30:00Z") // Returns: true
isdate("2023-12-25T10:30:00-05:00") // Returns: true
// Valid dates - US format
isdate("12/25/2023") // Returns: true
isdate("12-25-2023") // Returns: true
// Valid dates - Written format
isdate("Dec 25, 2023") // Returns: true
isdate("December 25, 2023") // Returns: true
isdate("25 Dec 2023") // Returns: true
// Invalid dates
isdate("2023-02-30") // Returns: false (Feb 30 doesn't exist)
isdate("2023-13-01") // Returns: false (month 13 doesn't exist)
isdate("not-a-date") // Returns: false
// Leap year handling
isdate("2024-02-29") // Returns: true (2024 is leap year)
isdate("2023-02-29") // Returns: false (2023 is not leap year)
// Partial dates
isdate("2023") // Returns: true (year only)
isdate("2023-12") // Returns: true (year-month)
```
### validatedate
Validate a date string (similar to ISDATE).
```typescript theme={null}
validatedate("2023-12-25") // Returns: true
validatedate("invalid") // Returns: false
```
### formatdate
Format a date string (specific format depends on implementation).
```typescript theme={null}
formatdate("2023-12-25", "YYYY-MM-DD") // Format date with pattern
```
## Logical Functions
### if
Conditional logic - returns second parameter if condition is true, third if false.
```typescript theme={null}
if(3 > 2, 1, 0) // Returns: 1
if(3 < 2, 1, 0) // Returns: 0
// With formulas
if(3 > 2, 1, 3 + 4) // Returns: 1
if(3 < 2, 1, 3 + 4) // Returns: 7
// Nested conditions
if(3 > 2, 1, if(3 < 2, 1, 0)) // Returns: 1
if(3 < 2, 1, if(3 < 2, 1, 0)) // Returns: 0
// With attributes
if({../division_budget} > 50000, "High Budget", "Low Budget")
if({ItemstoReview} = 0, "Complete", "Review Needed")
```
### isnull
Check if a value is null or empty.
```typescript theme={null}
isnull(3) // Returns: false
isnull("Hello") // Returns: false
isnull([]) // Returns: true
isnull({nonexistent_attribute}) // Returns: true
```
### ifnull
Return first parameter if not null, otherwise return second parameter.
```typescript theme={null}
ifnull({attribute_name}, "Default Value") // Use default if attribute is null
ifnull({optional_field}, 0) // Use 0 if field is null
```
### isblank
Check if a value is blank/empty.
```typescript theme={null}
isblank("") // Returns: true
isblank("hello") // Returns: false
isblank({empty_attribute}) // Returns: true
```
## Advanced Array Functions
### sumifs
Sum values that meet specific criteria (conditional sum).
```typescript theme={null}
// Sum values from attribute where condition is met
sumifs({amounts}, {categories}, "Sales")
```
### countifs
Count values that meet specific criteria (conditional count).
```typescript theme={null}
// Count items where condition is met
countifs({statuses}, "Complete")
```
## Special Functions
### confidence
Calculate confidence level (statistical function).
```typescript theme={null}
confidence(confidence_level, standard_deviation, sample_size)
```
## Complex Formula Examples
### Multi-Condition Validation
```typescript theme={null}
sum(
if({IS_OT_RVNU_AMT} = {IS_OT_RVNU_AMT_GC}, 0, 1),
if({IS_OT_COST_RVNU_AMT_GC} = {IS_OT_COST_RVNU_AMT}, 0, 1),
if({CDAD_IS_Gross_Profit} = {CDAD_IS_Gross_Profit_GC}, 0, 1),
0
)
```
This formula counts how many attribute pairs don't match, useful for data validation.
### Hierarchical Data Access
```typescript theme={null}
concat({../../grandparent_id}, "-", {../division_name}, "-", {department_name})
// Returns: "GP001-Sales Division-North Sales"
```
Builds a composite identifier from multiple levels of the data hierarchy.
### Conditional Formatting
```typescript theme={null}
if({../division_budget} > 75000,
concat("High Budget: ", {../division_budget}),
concat("Normal Budget: ", {../division_budget})
)
```
Formats output differently based on value thresholds.
### Date Range Validation
```typescript theme={null}
isbeforedate({start_date}, {end_date}) && isafterdate({check_date}, {start_date})
```
Checks if a date falls within a range.
## Best Practices
### 1. Use Parentheses for Clarity
```typescript theme={null}
// Good
(3 + 4) * 5
// Less clear
3 + 4 * 5
```
### 2. Reference Attributes Correctly
```typescript theme={null}
// Correct
{AttributeName}
{Parent/Child/AttributeName}
// Incorrect
AttributeName // Missing curly braces
{Attribute Name} // Spaces not allowed
```
### 3. Handle Null Values
```typescript theme={null}
// Good - provides fallback
ifnull({optional_field}, 0)
// Risky - may error if null
{optional_field} + 10
```
### 4. Use Appropriate Functions
```typescript theme={null}
// Good - precise decimal handling
decimalplaces({amount}, 2)
// Less precise
round({amount} * 100) / 100
```
### 5. Test Complex Formulas Incrementally
Build up complex formulas step by step:
1. `{revenue}`
2. `{revenue} - {costs}`
3. `({revenue} - {costs}) / {revenue}`
4. `decimalplaces(({revenue} - {costs}) / {revenue} * 100, 2)`
## Troubleshooting
### Common Issues
**Attribute Not Found**
* Ensure attribute name matches taxonomy exactly
* Check attribute path for hierarchical references
* Verify attribute exists in current context
**Type Mismatches**
* Can't perform math on strings: `"10" + 5` won't work
* Convert types explicitly when needed
* Use appropriate functions for data types
**Parent Navigation Errors**
* `Parent data object not found` - trying to go up too many levels
* Check data object hierarchy depth
* Ensure parent relationships exist
**Date Format Issues**
* Use consistent date formats
* ISO format (YYYY-MM-DD) is most reliable
* Test with isdate() before date operations
## Formula Evaluation Context
Formulas are evaluated with:
* **Current taxon** - The taxonomy node being evaluated
* **Current data object** - The data object containing attribute values
* **All data objects** - For hierarchical navigation and aggregations
* **Taxonomy** - For resolving attribute paths and types
## Related
Compute dropdown options dynamically using JavaScript formulas and service bridges
Apply formulas to validation rules, exceptions, and review UI formatting
Attach reactive scripts to group taxons that trigger when attributes change
Complete API reference for the GoJA JavaScript runtime used in formulas, scripts, and event subscriptions
Call external APIs from scripts via the service bridge proxy