References → Built-in Functions

Overview of built-in functions

Incorta provides the ability to create new formula column in the business schema view or within an insight on a dashboard using a comprehensive library of formula functions.

Aggregation Functions

Summarize the contents of a column of data. Here is a list of supported aggregation functions:

• average()
• median()
• min()
• max()
• sum()
• sum(), uses the groupBy parameter
• count()
• distinct()

Analytic Functions

Compute values over a group of rows and returns a single result for each row. Here is a list of supported analytic functions:

• ago()
• rank()
• denseRank()
• index()
• toDate()
• toDate() with ago()

Arithmetic Functions

Perform math operations on values. Here is a list of supported arithmetic functions:

• abs()
• ceil()
• floor()
• max()
• mod()
• min()
• rnd()
• round()
• exp()
• sqrt()
• trunc()

Boolean Functions

Evaluate one or more expressions and return a boolean value. Here is a list of supported boolean functions:

• startsWith()
• contains()
• endsWith()
• like()
• in
• inList
• isAlpha()
• isNan()
• isNull()
• isNumeric()
• and()
• or()
• not()
• between()

Conditional Statements

Evaluate and return the results of an expression depending on the result of evaluating a boolean expression. Here is a list of supported conditional statements:

• if()
• case()
• decode()
• caseContains()
• caseContains() (case sensitive)

Conversion Functions

Converts values from one type to another. Here is a list of supported conversion functions:

• epoch()
• int()
• long()
• parseDate()
• parseDouble()
• parseTimestamp()
• string()
• timestamp()
• double()
• toChar()
• year()
• quarter()
• quarter(date_timestamp exp)
• quarterDay()
• quarterDay(date_timestamp exp)
• quarterMonth()
• quarterMonth(date_timestamp exp)
• quarterWeek()
• quarterWeek(date_timestamp exp)
• month()
• monthName()
• monthName(date_timestamp exp)
• monthsBetween()
• monthWeek()
• monthWeek(date_timestamp exp)
• weekDay()
• weeknum()
• quarterStartDate()
• quarterEndDate()
• monthStartDate()
• monthEndDate()
• weekStartDate()
• weekEndDate()
• day()
• dayOfYear()
• hour()
• minute()
• second()
• second(date_timestamp exp)

Date Functions

Adjust values of type date or timestamp. Here is a list of supported date functions:

• addMilliseconds()
• addSeconds()
• addMinutes()
• addHours()
• addDays()
• addMonths()
• addQuarters()
• addYears()
• date()
• formatDate()
• minute()
• hour()
• day()
• month()
• year()
• daysBetween
• daysBetweenInt
• timeBetween
• weekday()
• weeknum()
• quarterStartDate()
• quarterEndDate()
• monthStartDate()
• monthEndDate()
• weekStartDate()
• weekEndDate()
• removeTime()
• dateTrunc()
• formatDuration()
• yearMonth
• yearMonth(date_timestamp exp)
• yearQuarter
• yearQuarter(date_timestamp exp)

Filter Functions

Filter data to those items before or after a specified date. It is also possible to filter the data in groups. Here are the supported filter functions:

• firstVersion()
• lastVersion()

Miscellaneous Functions

Various functions for finding values in table hierarchies, identifying a schema's last refresh time, and determining the row number in a query. Here is a list of supported miscellaneous functions:

• descendantOf()
• descendantOf() to a specified depth level
• lookup()
• schemaRefreshTime()
• rowNumber()

String Functions

Convert, truncate, and search within text strings. Here is a list of supported string functions:

• find()
• findLast()
• subString()
• subString() with a specified length
• splitPart()
• replace()
• lower()
• upper()
• lTrim()
• rTrim()
• trim()
• ifNull()
• length()
• bin()
• concat()
• repeat()
• reverse()
• left()
• right()

Query Functions

Retrieve column values or distinct values that satisfy optional filters. Here is a list of supported string functions:

• query()
• queryWithLimit()
• queryDistinct()
• queryDistinctWithLimit()