1. DataClarity
  2. DataClarity Unlimited Analytics
  3. Prepare datasets
  4. Refine datasets

Functions & macros

Avatar
Customer Care Team

Functions & macros

You can use the following functions and macros for adding calculations.

With date functions, you can manipulate dates in your dataset.

Function(syntax)Description

DATE_ADD(value, interval expr)

Adds a specified interval to a date.

The return type for this calculation must be set to Date.

Example:

DATE_ADD([Sales Date], interval '2' year) = 2019-01-24, where “Sales Date” is 2017-01-24

DATE_ADD('2019-01-24', interval '2' month) = 2019-03-24

DAY(dateexpr)

Returns the day of the month in the range 1 to 31 of dateexpr.

The calculation return type for this function must be set to Dimension.

Example:

DAY([Sales Date]) = 24, where “Sales Date” is 2021-01-24.

DAYNAME(dateexpr)

Returns the name of the weekday of dateexpr.

The calculation return type for this function must be set to Dimension.

Example:

DAYNAME('2021-01-26') = Tuesday

EXTRACT(extract_expression)

Returns a component of a timestamp, time, date, or interval. The extract expression is built as follows:

{component} FROM {timestamp | time | date | interval}, where “component” can be year, month, day, hour, minute, second.

The calculation return type for this function must be set to Dimension.

Example:

EXTRACT(day FROM DATE '2021-01-08') = 8

EXTRACT(year FROM DATE '2021-01-08') = 2021

EXTRACT(hour FROM TIME '17:12:28') = 17

EXTRACT(second FROM TIMESTAMP '2019-02-16 20:38:40') = 40

EXTRACT(day FROM [Sales Date]) = 8, where Sales Date is 2021-01-08

HOUR(dateexpr)

Returns the hour in the range of 0 to 23 of dateexpr.

The calculation return type for this function must be set to Dimension.

Example:

HOUR([Sales Date]) = 15, where “Sales Date” is Feb 27 2021 15:31:35.

MINUTE(dateexpr)

Returns the minute in the range of 0 to 59 of dateexpr.

The calculation return type for this function must be set to Dimension.

Example:

MINUTE([Sales Date]) = 31, where “Sales Date” is Feb 27 2021 15:31:35.

MONTH(dateexpr)

Returns the month for the date in the range 1 to 12 for dateexpr.

The calculation return type for this function must be set to Dimension.

Example:

MONTH([Sales Date]) = 1, where “Sales Date” is 2021-01-24.

MONTHNAME(dateexpr)

Returns the name of the month of dateexpr.

The calculation return type for this function must be set to Dimension.

Example:

MONTHNAME(‘2021-01-08’) = January

NOW()

Returns the current local system date and time in YYYY-MM-DD HH:mm:ss format.

The calculation type for this function must be set to Date or DateTime.

Example:

NOW() = 2021-01-26 18:46:27

SECOND(dateexpr)

Returns the second in the range of 0 to 59 of dateexpr.

The calculation return type for this function must be set to Dimension.

Example:

SECOND([Sales Date]) = 35, where “Sales Date” is Feb 27 2021 15:31:35.

TIMESTAMPADD(time_unit, interval, datetime_expression)

Adds an interval of time, in the given time units, to a datetime expression, where time_unit is any of the following: Nanosecond, Microsecond, Second, Minute, Hour, Day, Month, Year, Week, Quarter.

The return type for this calculation must be set to Date.

Example:

TIMESTAMPADD(YEAR,3,DATE '1982-05-06') = 1985-05-06

TIMESTAMPADD(YEAR,2, [Sales Date]) = 2018-05-06, where Sales Date is 2016-05-06

TIMESTAMPADD(MONTH,3, DATE '1982-05-06') = 1982-08-06

TIMESTAMPADD(MINUTE, 5, TIME '05:05:05') = 2021-01-26 07:10:05 (if the current time is 2021-01-26 02:05:00)

TO_CHAR(date, formatexpr)

Converts a date into a string with a given format.

The calculation return type for this function must be set to Dimension.

TO_CHAR([Sales Date], ‘YYYY:MM:DD’) = 2021:01:24

TO_DATE(datestr, formatexpr)

Given a date format specified in Java syntax, returns a date or NULL if conversion fails.

The return type for this calculation must be set to Date.

Example:

TO_DATE('2021-FEB-23', 'yyyy-MMM-dd') = 2021-02-23

YEAR(dateexpr)

Returns the year from a given date (dateexpr).

The calculation return type for this function must be set to Dimension.

Example:

YEAR([Sales Date]) = 2021, where “Sales Date” is 2021-01-24.

With mathematical functions, you can perform calculations on the numerical values in your dataset.

SyntaxDescription

ABS(expr)

Returns the absolute value of a number (expr).

Example:

ABS(-4) = 4

ABS([Profit]-[Planned Profit]) = The absolute values for all the rows for the profit variance.

ACOS(expr)

Returns the arc cosine of an expression (expr), in radians. Returns NULL if the expression is not in the range -1 to 1.

Example:

 ACOS(-1) = 3.14

ASIN(expr)

Returns the arc sine of an expression (expr), in radians. Returns NULL if the expression is not in the range -1 to 1.

Example:

ASIN(-1) = -1.57

ATAN(expr)

Returns the arc tangent of an expression (expr), in radians.

Example:

ATAN(180) = 1.57

CEILING(expr)

Rounds an expression (expr) to the nearest greater integer.

Examples:

CEILING(3.2) = 4

COS(expr)

Returns the cosine of an expression (expr).

Example:

COS(1) = 0.54

COT(expr)

Returns the cotangent of an expression (expr).

Example:

COT(14) = 0.14

DEGREES(expr)

Converts an expression (expr) given in radians to degrees.

Example:

DEGREES(3.15) = 180.48

EXP(expr)

Computes the exponential of an expression (expr).

Example:

EXP(2) = 7.39

FLOOR(expr)

Rounds an expression (expr) to the nearest lower integer.

Example:

FLOOR(3.7) = 3

LOG(expr)

Computes the natural logarithm of an expression (expr). In Excel, the natural algorithm denotes a log with base 10.

Example:

LOG(358) = 2.55 (Excel data source)

LOG(358) = 5.88 (Oracle data source)

MOD(expr1,expr2)

Returns the remainder of (expr1) divided by (expr2).

Example:

MOD(10,3) = 1

POWER(expr1, expr2)

Raises an expression to the specified power: expr1expr2

Example:

POWER(5, 2) = 25

POWER(5, 3) = 125

RADIANS(expr)

Converts an expression (expr) given in degrees into radians.

Example:

RADIANS(180) = 3.14

ROUND(expr1, expr2)

Rounds an expression (expr1) to a specified decimal (expr2).

Example:

ROUND(3.145689, 2) = 3.15

ROUND(expr)

Rounds an expression (expr) to the nearest integer.

Example:

ROUND(3.145689) = 3

SIGN(expr)

Returns the sign of an expression (expr): -1, 0, or 1.

Example:

SIGN(-23) = -1

SIGN(23) = 1

SIGN(0) = 0

SIN(expr)

Returns the sine of an expression (expr) given in radians.

Example:

SIN(12) = -0.54

SIN(3.14/2) = 1.00

SQRT(expr)

Computes the square root of an expression (expr).

Example:

SQRT(25) = 5

TAN(expr)

Returns the tangent of an expression (expr) given in radians.

Example:

TAN(25) = -0.13

TAN(3.14/4) = 1.00

With string functions, you can manipulate string data (for example, text).

SyntaxDescription

ASCII(expr)

Returns the ASCII code of the first (leftmost) character in a string (expr).

Example:

ASCII(‘ma’) = 109

CHAR(expr)

Returns a character that has the ASCII code of an expression (expr).

Example:

CHAR(109) = m

CHAR_LENGTH (expr)

Returns the length of a string (expr), as the number of characters.

Example:

CHAR_LENGTH(‘Floor’) = 5

COALESCE(expr1, expr2, ...)

Returns the first non-null expression in the list. If all occurrences are null, then the function returns null.

Example:

COALESCE(NULL, 'world') = world

COALESCE('', 'world') = [empty string]

CONCAT(expr1, expr2)

Joins two or more strings into one string.

Example:

CONCAT(‘Floor’, ‘ Care’) = Floor Care

DOUBLE_METAPHONE(expr)

Returns a phonetic encoding for the input string (expr) by using the Double Metaphone algorithm. In English, for example, the algorithm removes silent letters and vowels inside a word to normalize inconsistent spellings for better matching. This function is useful for fuzzy matching between string values.

Example:

DOUBLE_METAPHONE(‘Floor Care’) = FLRK

LCASE(expr)

Converts a string (expr) into lowercase.

Example:

LCASE('HELLO') = hello

LEFT(expr, len)

Returns a specified number of the leftmost characters from the string.

Example:

LEFT('Hello', 2) = He

LENGTH(expr)

Returns the length of a string (expr), as the number of characters.

Example:

LENGTH('Hello') = 5

LOWER(expr)

Converts a string (expr) into lowercase.

Example:

LOWER('HELLO') = hello

LTRIM(expr)

Removes whitespace characters at the beginning of a string (expr).

Example:

LTRIM(‘ hello') = hello

METAPHONE(expr)

Returns a phonetic encoding for the input string (expr) by using the Metaphone algorithm. In English, for example, the algorithm removes silent letters and vowels inside a word to normalize inconsistent spellings for better matching. This function is useful for fuzzy matching between string values.

Example:

METAPHONE(‘Floor Care’) = FLRK

POSITION(substr in str)

Returns the starting position (an integer value) of the first occurrence of the substring (substr) within the search string (str).

Example:

POSITION('World' in 'Hello World!') = 7

REFINED_SOUNDEX(expr)

Returns a phonetic encoding for the input string (expr) by using the Refined Soundex algorithm. The encoding is a refined Soundex code based on the English word pronunciation. The algorithm is used to normalize inconsistencies between spellings for better matching (fuzzy matching between string values).

Example:

REFINED_SOUNDEX(‘Floor Care’) = F27093090

REGEXP_MATCHES(str, regexp)

Returns 'true' if a string (str) matches a regexp pattern or ‘false’ if the given string does not match the regexp pattern.

Example:

REGEXP_MATCHES('abc', 'abc|def') = true

REGEXP_MATCHES('Floor', '.loor') = true

REGEXP_MATCHES('8', '[0-9]') = true

REGEX_REPLACE(str, oldstr, newstr)

In a string (str), replaces all occurrences of an old string (oldstr) with a new string (newstr).

Example:

REPLACE(‘Floor Care’, 'Floor', ‘Flower’) = Flower Care

REPLACE(str, oldstr, newstr)

In a string (str), replaces all occurrences of an old string (oldstr) with a new string (newstr).

Example:

REPLACE(‘Floor Care’, 'Floor', ‘Flower’) = Flower Care

REVERSE(str)

Spells the string (str) backwards.

Example:

REVERSE(‘Floor Care’) = areC roolF

RIGHT(expr, len)

Returns a specified number (len) of the rightmost characters from a string (expr).

Example:

RIGHT('Hello', 2) = lo

RTRIM(expr)

Removes trailing spaces from the string (expr).

Example:

RTRIM(‘ hello ') = ‘ hello’

SOUNDEX(expr)

Returns a phonetic encoding for the input string (expr) by using the Soundex algorithm. The encoding is a four-character code based on the English word pronunciation. The algorithm is used to normalize inconsistencies between spellings for better matching (fuzzy matching between string values).

Example:

SOUNDEX(‘Floor Care’) = F462

SPLIT_PART(str, delimiter, part)

Splits a string (str) based on the specified delimiter and returns the part at the specified position. The part number to return starts with 1.

Example:

SPLIT_PART('John Smith', ' ' , 2) = 'Smith'

STRPOS(str, substr)

Returns the starting position (integer value) of a substring (substr) within the search string (str).

Example:

STRPOS('FloorCare', 'Care') = 6

SUBSTR(str, start, len)

From a string (str), extracts a substring of a certain length (len) starting at a certain position (start).

Example:

SUBSTR('Floor Care', 7, 4) = Care

SUBSTR(str, start)

From a string (str), extracts a substring starting at a certain position (start).

Example:

SUBSTR('Floor Care', 5) = r Care

SUBSTR('Floor Care', 1) = Floor Care

TRIM(type trimchars from expr)

Removes specific characters (trimchars) of a given type (type) from a string (expr).

Example:

TRIM(leading 'F' from 'Floor Care') = loor Care

TRIM(trailing 'e' from 'Floor Care') = Floor Car

TRIM(both '_' from 'Floor Care_') = Floor Care

TRIM(leading '1' from '112') = 2

TRIM(expr)

Removes leading and trailing characters from a string (expr).

Example:

TRIM(‘Floor Care’) = Floor Care

UCASE(expr)

Converts a string (expr) into uppercase.

Example:

UCASE(‘floor care’) = FLOOR CARE

UPPER(expr)

Converts a string (expr) into uppercase.

Example:

UPPER(‘floor care’) = FLOOR CARE

For the Data Science functions, see Data science built-in algorithms.

You can use the following macros in your calculations to get user-related information. For example, you can use user macros to set up row-level security based on the user logged into the platform. For details, see User macros in filters and calculations.

MacroDescription
${user.username}

Returns the username for the current user.

Example:

${user.username} = j.smith

${user.firstname}

Returns the first name for the current user.

Example:

${user.firstname} = John

${user.lastname}

Returns the last name for the current user.

Example:

${user.firstname} = Smith

${user.email}

Returns the email for the current user.

Example:

${user.email} = j.smith@company.com

${user.groups}

Returns the groups for the current user.

Example:

${user.groups} = Group 1, Group 2

${user.tenant}

Returns the tenant for the current user.

Example:

${user.tenant} = Tenant 1

Articles in this section

See more
Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.