On This Page
> resources > docs > expressions > Advanced Functions

Advanced Functions

These function families complete the expression language. Each group below lists its functions with their parameters, return type and examples.

Text and Pattern Functions

Regular Expression Functions

Functions for matching and replacing patterns with regular expressions.

regexMatch

(text, pattern)

Tests whether a string matches a regular expression pattern.

Parameters
text string The string to test
pattern string The regular expression pattern
Returns boolean
Examples
regexMatch("abc123", "[0-9]+") → true
regexMatch("hello", "^[0-9]+$") → false
See Also
regexExtractregexReplacecontains

regexReplace

(text, pattern, replacement)

Replaces every occurrence of a regular expression pattern in a string.

Parameters
text string The input string
pattern string The regular expression pattern to match
replacement string The replacement string
Returns string
Examples
regexReplace("abc123def456", "[0-9]+", "#") → abc#def#
regexReplace("hello world", "\s+", " ") → hello world
See Also
regexMatchregexExtractreplace

regexExtract

(text, pattern, group?)

Extracts the first match of a regular expression pattern from a string. You can optionally extract a specific capture group.

Parameters
text string The input string
pattern string The regular expression pattern
group (optional) number The capture group index, 0 for the full match (default: 0)
Returns string
Examples
regexExtract("abc123def", "[0-9]+") → 123
regexExtract("2025-01-15", "(\d{4})-(\d{2})", 1) → 2025
See Also
regexMatchregexReplace

String Utilities

Additional string manipulation functions.

repeat

(text, count)

Repeats text a specified number of times.

Parameters
text string Text to repeat
count number Number of repetitions
Returns string
Examples
repeat("ab", 3) → "ababab"

countOccurrences

(text, search)

Counts non-overlapping occurrences of a substring.

Parameters
text string Text to search
search string Substring to find
Returns number
Examples
countOccurrences("abcabc", "abc") → 2

split

(text, delimiter, index)

Splits text by a delimiter and returns the nth part, starting at 1.

Parameters
text string Text to split
delimiter string Delimiter to split on
index number 1-based index of the part to return
Returns string
Examples
split("a-b-c", "-", 2) → "b"

exact

(a, b)

Case-sensitive string comparison. Returns true if both strings are identical.

Parameters
a string First string
b string Second string
Returns boolean
Examples
exact("Hello", "hello") → false

Date and Duration Helpers

Duration Helpers

Converts millisecond durations to common time units.

durationInDays

(ms)

Converts milliseconds to days.

Parameters
ms number Duration in milliseconds
Returns number
Examples
durationInDays(86400000) → 1
durationInDays(caseThroughputTime) → Throughput time in days

durationInHours

(ms)

Converts milliseconds to hours.

Parameters
ms number Duration in milliseconds
Returns number
Examples
durationInHours(3600000) → 1
durationInHours(stepDuration) → Step duration in hours

durationInMinutes

(ms)

Converts milliseconds to minutes.

Parameters
ms number Duration in milliseconds
Returns number
Examples
durationInMinutes(60000) → 1
durationInMinutes(waitingTime) → Waiting time in minutes

durationInSeconds

(ms)

Converts milliseconds to seconds.

Parameters
ms number Duration in milliseconds
Returns number
Examples
durationInSeconds(1500) → 1.5

Date Construction

Creates dates from parts or parses strings into dates.

date

(year, month, day)

Creates a UTC date from a year, month, and day.

Parameters
year number The year
month number The month (1-12)
day number The day of the month
Returns date
Examples
date(2024, 1, 1) → 1704067200000
date(2024, 6, 15) → June 15, 2024

toDate

(value, format?)

Parses a value into a date. Accepts numbers, such as epoch milliseconds, or strings.

Parameters
value any The value to convert to a date
format (optional) string Date format pattern (optional)
Returns date
Examples
toDate(1704067200000) → 1704067200000
toDate("2024-01-15") → January 15, 2024
toDate("31/12/2024 23:45:59", "dd/MM/yyyy HH:mm:ss") → 1735688759000
Notes

When a format is provided, use the same supported tokens as formatDate. Parsing is UTC-based.

today

()

Returns the current date at midnight UTC.

Returns date
Examples
today() → Current date at midnight

Date Period Boundaries

Truncates or rounds dates to period boundaries.

startOfDay

(date)

Truncates a date to the start of the day, midnight.

Parameters
date date The date to truncate
Returns date
Examples
startOfDay(StartTime) → Midnight of the same day

startOfWeek

(date)

Returns the start of the week, Monday.

Parameters
date date The date
Returns date
Examples
startOfWeek(StartTime) → Monday of the same week

startOfMonth

(date)

Returns the first day of the month.

Parameters
date date The date
Returns date
Examples
startOfMonth(StartTime) → First of the month

startOfYear

(date)

Returns January 1 of the year.

Parameters
date date The date
Returns date
Examples
startOfYear(StartTime) → January 1st

endOfWeek

(date)

Returns the end of the week, Sunday at 23:59:59.

Parameters
date date The date
Returns date
Examples
endOfWeek(StartTime) → Sunday end of day

endOfMonth

(date)

Returns the end of the month at 23:59:59.

Parameters
date date The date
Returns date
Examples
endOfMonth(StartTime) → Last day of the month, end of day

endOfYear

(date)

Returns December 31 at 23:59:59.

Parameters
date date The date
Returns date
Examples
endOfYear(StartTime) → December 31st, end of day

Date Shortcuts

Convenience functions for common date difference and addition operations.

daysBetween

(date1, date2)

Returns the difference in days between two dates.

Parameters
date1 date Start date
date2 date End date
Returns number
Examples
daysBetween(StartTime, EndTime) → Duration in days between two dates

hoursBetween

(date1, date2)

Returns the difference in hours between two dates.

Parameters
date1 date Start date
date2 date End date
Returns number
Examples
hoursBetween(StartTime, EndTime) → Duration in hours between two dates

minutesBetween

(date1, date2)

Returns the difference in minutes between two dates.

Parameters
date1 date Start date
date2 date End date
Returns number
Examples
minutesBetween(StartTime, EndTime) → Duration in minutes between two dates

secondsBetween

(date1, date2)

Returns the difference in seconds between two dates.

Parameters
date1 date Start date
date2 date End date
Returns number
Examples
secondsBetween(StartTime, EndTime) → Duration in seconds between two dates

addDays

(date, days)

Adds days to a date.

Parameters
date date Base date
days number Number of days to add
Returns date
Examples
addDays(StartTime, 5) → Date 5 days later

addHours

(date, hours)

Adds hours to a date.

Parameters
date date Base date
hours number Number of hours to add
Returns date
Examples
addHours(StartTime, 3) → Date 3 hours later

addMinutes

(date, minutes)

Adds minutes to a date.

Parameters
date date Base date
minutes number Number of minutes to add
Returns date
Examples
addMinutes(StartTime, 30) → Date 30 minutes later

addSeconds

(date, seconds)

Adds seconds to a date.

Parameters
date date Base date
seconds number Number of seconds to add
Returns date
Examples
addSeconds(StartTime, 45) → Date 45 seconds later

endOfDay

(date)

Returns the end of the day, 23:59:59.999, for a given date.

Parameters
date date Input date
Returns date
Examples
endOfDay(StartTime) → 23:59:59 on the same day

startOfQuarter

(date)

Returns the first day of the quarter for a given date.

Parameters
date date Input date
Returns date
Examples
startOfQuarter(StartTime) → 2024-04-01 for a June date

endOfQuarter

(date)

Returns the last moment of the quarter for a given date.

Parameters
date date Input date
Returns date
Examples
endOfQuarter(StartTime) → 2024-06-30T23:59:59Z for a June date

Logic Helpers

Multi-Way Conditional

Matches a value against multiple cases.

switch

(value, match1, result1, ..., default?)

Matches a value against a list of cases and returns the corresponding result. Similar to a multi-way if.

Parameters
value any The value to match
match1, result1, ... any Pairs of match values and results
default (optional) any Default result if no match, optional
Returns any
Examples
switch("a", "a", 1, "b", 2, 0) → 1
switch(Status, "Open", 1, "Closed", 2, 0) → 1 if Open, 2 if Closed, else 0
Notes

Takes variadic arguments: switch(value, match1, result1, match2, result2, ..., default)

Range Check

Checks whether values fall within ranges or clamps them.

between

(value, low, high)

Returns true if a value is between low and high, inclusive.

Parameters
value number The value to check
low number Lower bound
high number Upper bound
Returns boolean
Examples
between(5, 1, 10) → true
between(Amount, 100, 1000) → true if Amount is between 100 and 1000

clamp

(value, min, max)

Restricts a value to a [min, max] range.

Parameters
value number The value to clamp
min number Minimum value
max number Maximum value
Returns number
Examples
clamp(15, 0, 10) → 10
clamp(Score, 0, 100) → Score restricted to 0-100

Let Binding

Binds intermediate values to names for use in complex expressions.

let

(name, value, body)

Binds a value to a name and evaluates the body expression with that binding available.

Parameters
name string Variable name, string literal
value any Expression to bind
body any Expression using the bound variable
Returns any
Examples
let("avg", caseAvg(Amount), Amount - avg) → Deviation from case average
let("x", Amount * 2, let("y", x + 3, x * y)) → Nested let bindings

Flow, Activity and SLA Functions

Preceded-By Predicates

Checks whether activities are preceded by other activities in a case.

directlyPrecededBy

(activity, predecessor)

Returns true if the first activity is directly preceded by the second activity in the case.

Parameters
activity string The activity to check
predecessor string The expected predecessor activity
Returns boolean
Examples
directlyPrecededBy("Approve", "Review") → true if Review directly precedes Approve
See Also
eventuallyPrecededBydirectlyFollowedBy

eventuallyPrecededBy

(activity, predecessor)

Returns true if the first activity is eventually preceded by the second activity in the case.

Parameters
activity string The activity to check
predecessor string The expected predecessor activity
Returns boolean
Examples
eventuallyPrecededBy("Ship", "Order") → true if Order appears before Ship in the case
See Also
directlyPrecededByeventuallyFollowedBy

Rework Detection

Identifies rework patterns where activities repeat within a case.

isRework

Property

True if the current activity occurred earlier in the case.

Type boolean

reworkCount

Property

Total number of repeated activity executions in the case.

Type number

activityOccurrence

Property

The occurrence number of this activity in the case: 1 for the first time, 2 for the second, and so on.

Type number

Activity Queries

Queries activity presence and counts within a case.

hasActivity

(activityName)

Returns true if the named activity appears anywhere in the case.

Parameters
activityName string Name of the activity to check
Returns boolean
Examples
hasActivity("Approve") → true if the case contains an Approve activity

activityCount

(activityName)

Returns the number of times the named activity appears in the case.

Parameters
activityName string Name of the activity to count
Returns number
Examples
activityCount("Review") → Number of Review activities in the case

Inter-Activity Timing

Measures time between specific activities in a case.

timeBetween

(activityA, activityB)

Returns the time between the first occurrence of activity A and the first occurrence of activity B.

Parameters
activityA string First activity
activityB string Second activity
Returns number
Examples
timeBetween("Submit", "Approve") → Time in ms from Submit to Approve

timeSinceActivity

(activityName)

Time elapsed since the last occurrence of the named activity before the current event.

Parameters
activityName string Activity name
Returns number
Examples
timeSinceActivity("Start") → Time since the last Start activity

timeToActivity

(activityName)

Time until the next occurrence of the named activity after the current event.

Parameters
activityName string Activity name
Returns number
Examples
timeToActivity("Complete") → Time until the next Complete activity

SLA Functions

Service Level Agreement monitoring and breach detection.

slaBreached

(duration, threshold)

Returns true if a duration exceeds the SLA threshold.

Parameters
duration number Actual duration
threshold number SLA threshold
Returns boolean
Examples
slaBreached(100000, 86400000) → false
slaBreached(caseThroughputTime, 86400000) → true if case exceeds 24-hour SLA

slaRemaining

(startDate, slaDuration)

Returns the time remaining before an SLA breach, or a negative value after the breach.

Parameters
startDate date Start timestamp
slaDuration number SLA duration in milliseconds
Returns number
Examples
slaRemaining(caseStartTime, 172800000) → Time remaining for 48-hour SLA

caseAge

Property

Time elapsed since the case started, in milliseconds from the first event to now.

Type number

Self-Loop Detection

Detects and counts self-loop patterns where the same activity occurs consecutively.

isSelfLoop

Property

True if the current activity is the same as the previous activity in the case.

Type boolean

selfLoopCount

Property

Number of consecutive repetitions of the same activity in the current case.

Type number

Resource Analytics

Detects resource handoffs and changes within a case.

isResourceChange

(expr)

Returns true if the expression value changed from the previous event.

Parameters
expr any Column expression to check for changes
Returns boolean
Examples
isResourceChange(Resource) → true when resource differs from previous event

resourceHandoffCount

(expr)

Counts value changes across the case.

Parameters
expr any Column expression whose changes you want to count
Returns number
Examples
resourceHandoffCount(Resource) → 2 for Alice→Alice→Bob→Bob→Alice
Notes

Not available in Athena-backed expressions yet. For backend queries, use isResourceChange(expr) together with caseCountIf(...) in a derived attribute.

Statistics, Ranking and Value Changes

Ranking Functions

Ranks events by expression values within a case or across all events.

caseRank

(expr)

Rank of the current event within the case, ordered by expression value, with gaps for ties.

Parameters
expr number Expression to rank by
Returns number
Examples
caseRank(Amount) → Rank by Amount within the case

caseDenseRank

(expr)

Dense rank within the case, with no gaps for ties.

Parameters
expr number Expression to rank by
Returns number
Examples
caseDenseRank(Amount) → Dense rank by Amount within the case

allRank

(expr)

Rank across all events.

Parameters
expr number Expression to rank by
Returns number
Examples
allRank(Duration) → Global rank by Duration

allPercentRank

(expr)

Percent rank across all events, from 0 to 1.

Parameters
expr number Expression to rank by
Returns number
Examples
allPercentRank(Duration) → Percentile rank (0-1)

Value Change Detection

Detects when values change between events.

changed

(expr)

Returns true if the expression value differs from the previous event.

Parameters
expr any Expression to check for change
Returns boolean
Examples
changed(Resource) → true if the resource changed from the previous event

caseChanges

(expr)

Counts value changes across all events in the case.

Parameters
expr any Expression to check for changes
Returns number
Examples
caseChanges(Resource) → Number of resource handoffs in the case
Notes

Not available in Athena-backed expressions yet. For backend queries, use changed(expr) together with caseCountIf(...) in a derived attribute.

Conditional Running Aggregates

Running aggregates with conditions, calculated through the current event.

caseRunningSumIf

(expr, condition)

Running conditional sum through the current event.

Parameters
expr number Expression to sum
condition boolean Condition to filter events
Returns number
Examples
caseRunningSumIf(Amount, Status == "Paid") → Running sum of Amount where Status is Paid

caseRunningCountIf

(condition)

Running conditional count through the current event.

Parameters
condition boolean Condition to count
Returns number
Examples
caseRunningCountIf(Activity == "Error") → Running count of Error activities

caseRunningAvgIf

(expr, condition)

Running conditional average through the current event.

Parameters
expr number Expression to average
condition boolean Condition to filter events
Returns number
Examples
caseRunningAvgIf(Duration, Priority == "High") → Running average duration of high-priority events

Elapsed Time Properties

Properties for measuring progress and elapsed time within a case.

elapsedSinceStart

Property

Time elapsed since the case started, in milliseconds.

Type number

remainingInCase

Property

Remaining time until the case ends, in milliseconds.

Type number

percentComplete

Property

Progress through the case as a fraction, from 0 to 1.

Type number

Extended Conditional Aggregates

Additional conditional aggregate functions for median, standard deviation, and distinct counts.

caseMedianIf

(expr, condition)

Conditional median across case events where the condition is true.

Parameters
expr number Value expression
condition boolean Filter condition
Returns number
Examples
caseMedianIf(Amount, Activity == "Review") → Median amount for Review activities

allMedianIf

(expr, condition)

Conditional median across all events where the condition is true.

Parameters
expr number Value expression
condition boolean Filter condition
Returns number
Examples
allMedianIf(Amount, Status == "Complete") → Global median for complete events

caseStdDevIf

(expr, condition)

Conditional standard deviation across case events.

Parameters
expr number Value expression
condition boolean Filter condition
Returns number
Examples
caseStdDevIf(Duration, Activity == "Review") → StdDev of Review durations in case

allStdDevIf

(expr, condition)

Conditional standard deviation across all events.

Parameters
expr number Value expression
condition boolean Filter condition
Returns number
Examples
allStdDevIf(Amount, Priority == "High") → Global stddev for high priority

caseCountDistinctIf

(expr, condition)

Conditional count of distinct values across case events.

Parameters
expr any Value expression
condition boolean Filter condition
Returns number
Examples
caseCountDistinctIf(Category, Amount > 100) → Distinct categories with large amounts

allCountDistinctIf

(expr, condition)

Conditional count of distinct values across all events.

Parameters
expr any Value expression
condition boolean Filter condition
Returns number
Examples
allCountDistinctIf(User, Department == "Sales") → Distinct users in Sales department

Extended Running Aggregates

Additional running aggregate functions for median, standard deviation, and conditional minimum and maximum.

caseRunningMedian

(expr)

Running median of values through the current event.

Parameters
expr number Value expression
Returns number
Examples
caseRunningMedian(Amount) → Running median amount

caseRunningStdDev

(expr)

Running standard deviation through the current event.

Parameters
expr number Value expression
Returns number
Examples
caseRunningStdDev(Amount) → Running standard deviation

caseRunningMinIf

(expr, condition)

Running conditional minimum through the current event.

Parameters
expr number Value expression
condition boolean Filter condition
Returns number
Examples
caseRunningMinIf(Amount, Activity == "Review") → Running min of Review amounts

caseRunningMaxIf

(expr, condition)

Running conditional maximum through the current event.

Parameters
expr number Value expression
condition boolean Filter condition
Returns number
Examples
caseRunningMaxIf(Amount, Priority == "High") → Running max for high priority

JSON Functions

JSON Extraction

Extracts values from JSON strings using path expressions.

jsonExtract

(json, path)

Extracts a value from a JSON string using a JSONPath expression.

Parameters
json string JSON string
path string JSONPath expression, for example, "$.field"
Returns any
Examples
jsonExtract("{\"name\":\"John\"}", "$.name") → John

jsonValue

(json, path)

Extracts a scalar value from a JSON string as a string.

Parameters
json string JSON string
path string JSONPath expression
Returns string
Examples
jsonValue("{\"age\":30}", "$.age") → "30"

jsonArrayLength

(json)

Returns the length of a JSON array.

Parameters
json string JSON array string
Returns number
Examples
jsonArrayLength("[1,2,3]") → 3

Related Documentation