Formulas Guide - Kodexa Developer Portal

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

// Basic arithmetic
+ 4          // Returns: 7
- 3         // Returns: 7
* 5          // Returns: 20
/ 3         // Returns: 5

Attribute References

Reference attributes from your data objects using curly braces:

{attributeName}              // Single attribute value
{IS_OT_RVNU_AMT}            // Specific attribute by name
{ClaimOverview/ClaimID}     // Hierarchical path to attribute

Access parent data object attributes using ../:

{../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:

{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

// Equality
= 3           // true
= 4           // false
!= 4          // true
!= 3          // false

// Comparison
< 4           // true
<= 3          // true
> 2           // true
>= 3          // true

Logical Operators

// 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.

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.

sumabs(-1, 2, -3)                 // Returns: 6
sumabs()                          // Returns: 0
sumabs({deviation1}, {deviation2}, {deviation3})  // Sum absolute deviations

average

Calculate the mean of values.

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.

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.

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.

count(1, 2, 3, 4)            // Returns: 4
count({ClaimOverview/Claimdetails/ClaimID})  // Count attribute occurrences

abs

Get absolute value (remove negative sign).

abs(-1)                      // Returns: 1
abs(5)                       // Returns: 5

ceil

Round up to nearest integer.

ceil(1.5)                    // Returns: 2
ceil(1.1)                    // Returns: 2

floor

Round down to nearest integer.

floor(1.8)                   // Returns: 1
floor(1.9)                   // Returns: 1

round

Round to nearest integer.

round(1.5)                   // Returns: 2
round(1.4)                   // Returns: 1

decimalplaces

Round to a specific number of decimal places with optional rounding mode.

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.

stddeviation(1, 2, 3, 4, 5)          // Returns: ~1.58
stddeviation(10, 20, 30, 40, 50)     // Returns: ~15.81

String Functions

concat

Combine multiple strings into one.

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.

uppercase("hello")                   // Returns: ["HELLO"]
uppercase("hello", "world")          // Returns: ["HELLO", "WORLD"]

lowercase

Convert text to lowercase.

lowercase("HELLO")                   // Returns: ["hello"]
lowercase("HELLO", "WORLD")          // Returns: ["hello", "world"]

trim

Remove leading and trailing whitespace.

trim(" hello ")                      // Returns: ["hello"]
trim("  text  ")                     // Returns: ["text"]

substring

Extract part of a string.

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.

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.

// 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.

contains("hello", "ell")             // Returns: true
contains("hello", "xyz")             // Returns: false

startswith

Check if a string starts with a substring.

startswith("hello", "he")            // Returns: true
startswith("hello", "lo")            // Returns: false

endswith

Check if a string ends with a substring.

endswith("hello", "lo")              // Returns: true
endswith("hello", "he")              // Returns: false

replace

Replace text within a string.

replace("hello", "e", "a")           // Returns: "hallo"
replace("hello world", "world", "there")  // Returns: "hello there"

split

Split a string into an array by delimiter.

split("hello,world", ",")            // Returns: ["hello", "world"]
split("a|b|c", "|")                 // Returns: ["a", "b", "c"]

regex

Test if a string matches a regular expression pattern.

// 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.

// 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.

daysbetween("2023-01-01", "2023-01-10")  // Returns: 9
daysbetween("2023-12-25", "2024-01-01")  // Returns: 7

weeksbetween

Calculate weeks between two dates.

weeksbetween("2023-01-01", "2023-01-15")  // Returns: 2
weeksbetween("2023-01-01", "2023-02-01")  // Returns: ~4

monthsbetween

Calculate months between two dates.

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).

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).

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.

// 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).

validatedate("2023-12-25")                // Returns: true
validatedate("invalid")                   // Returns: false

formatdate

Format a date string (specific format depends on implementation).

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.

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.

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.

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.

isblank("")                              // Returns: true
isblank("hello")                         // Returns: false
isblank({empty_attribute})               // Returns: true

Advanced Array Functions

sumifs

Sum values that meet specific criteria (conditional sum).

// Sum values from attribute where condition is met
sumifs({amounts}, {categories}, "Sales")

countifs

Count values that meet specific criteria (conditional count).

// Count items where condition is met
countifs({statuses}, "Complete")

Special Functions

confidence

Calculate confidence level (statistical function).

confidence(confidence_level, standard_deviation, sample_size)

Complex Formula Examples

Multi-Condition Validation

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

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

if({../division_budget} > 75000,
  concat("High Budget: ", {../division_budget}),
  concat("Normal Budget: ", {../division_budget})
)

Formats output differently based on value thresholds.

Date Range Validation

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

// Good
(3 + 4) * 5

// Less clear
3 + 4 * 5

2. Reference Attributes Correctly

// Correct
{AttributeName}
{Parent/Child/AttributeName}

// Incorrect
AttributeName           // Missing curly braces
{Attribute Name}        // Spaces not allowed

3. Handle Null Values

// Good - provides fallback
ifnull({optional_field}, 0)

// Risky - may error if null
{optional_field} + 10

4. Use Appropriate Functions

// 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:

{revenue}
{revenue} - {costs}
({revenue} - {costs}) / {revenue}
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

Introduction

Deployment

Notebooks

Cookie Cutters

Formulas

Data Forms

​Basic Syntax

​Simple Expressions

​Attribute References

​Parent Navigation

​Attribute Expressions (Filtering)

​Comparison Operators

​Logical Operators

​Mathematical Functions

​sum

​sumabs

​average

​max

​min

​count

​abs

​ceil

​floor

​round

​decimalplaces

​stddeviation

​String Functions

​concat

​uppercase

​lowercase

​trim

​substring

​length / strlen

​len

​contains

​startswith

​endswith

​replace

​split

​regex

​Date Functions

​datemath

​daysbetween

​weeksbetween

​monthsbetween

​isbeforedate

​isafterdate

​isdate

​validatedate

​formatdate

​Logical Functions

​if

​isnull

​ifnull

​isblank

​Advanced Array Functions

​sumifs

​countifs

​Special Functions

​confidence

​Complex Formula Examples

​Multi-Condition Validation

​Hierarchical Data Access

​Conditional Formatting

​Date Range Validation

​Best Practices

​1. Use Parentheses for Clarity

​2. Reference Attributes Correctly

​3. Handle Null Values

​4. Use Appropriate Functions

​5. Test Complex Formulas Incrementally

​Troubleshooting

​Common Issues

​Formula Evaluation Context