--- schema_version: "1" id: ed2a7ca67864bd19ad31090dfb1b951f7ff072e77b0394e768bc1af60dd170a4 title: Calculated Dimension Functions collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/mktg/264-0-0/dato_dimensions_functions.md canonical_url: https://help.salesforce.com/s/articleView?id=mktg.dato_dimensions_functions.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: 86ae57a95360388c53ab7dad84d92f3515115f641d71662951ba4032533c00a4 description: Marketing Cloud Intelligence provides basic functions you can use to create dynamic calculated dimensions products: - Marketing - Marketing Cloud Intelligence release: "264" --- # Calculated Dimension Functions Marketing Cloud Intelligence provides basic functions you can use to create dynamic calculated dimensions The functions are divided into these categories: - Number Functions - Text Functions - Date Functions - Aggregation Functions - Logical Functions - Type Conversion - Content Functions These tables summarize the available functions for calculated dimensions. ## Number Functions | FUNCTION NAME | SYNTAX | DESCRIPTION | | --- | --- | --- | | ABS | ABS(number) | Returns the absolute value of a number. | | CEILING | CEILING(number, significance) | Rounds the number up, away from zero, to the nearest multiple significance. | | FLOOR | FLOOR(number, significance) | Rounds the number down toward zero, to the nearest multiple significance. | | RAND | RAND() | Gets a random number between 0 and 1. | | ROUND | ROUND(number) | Rounds a number to the nearest integer. | | ROUND | ROUND(number, num\_digits) | Rounds a number to a specified number of digits. | ## Text Functions
FUNCTION NAME SYNTAX DESCRIPTION
CHAR CHAR(number) Returns a character specified by a number.
COALESCE COALESCE([value1, value2,...]) Returns the first value from the list that isn’t empty.
CODE CODE(text) Returns a numeric code for the first character in a text string.
COUNT_CHAR COUNT_CHAR(text, charToCount) Counts how many times a specific character appears in a text.
EXTRACT EXTRACT(text, delimiter, position) Extracts from the given text the substring, at the given position, after splitting by the given delimiter. For example, EXTRACT(‘a,b,c’,’_’,1) returns ‘b’.
FIND FIND(findText, withinText) Returns the starting position of findText withinText. Returns -1 if withinText doesn’t contain findText.
FUZZYMATCH FUZZYMATCH(valueToMatch, array, threshold) Searches for a string in an array of values that potentially match using duplicate-detection algorithms that calculate the similarity of two streams of data. Returns the matched string from the array if there’s a match or, if no matches are found, returns an empty string. For example, FUZZYMATCH("ABC Company",{"ABC Company Inc","Apple Inc","IBM Company"},0.8) returns "ABC Company Inc". The threshold parameter must be a value between 0 and 1. A threshold of 1 means an exact match.
GET_EXCHANGE_RATE GET_EXCHANGE_RATE(date, fromISO, toISO)

Get the exchange rate for the given currencies and date.

The ‘date’ variable in the formula can be a mapped date field or a date function

For example:

  • GET_EXCHANGE_RATE([Day], 'USD', 'GBP')

where [Day] is a mapped date field

  • GET_EXCHANGE_RATE(DATE('2015','8','17'),'USD','GBP')

where DATE('YYYY','MM','DD') is a date function.

  • GET_EXCHANGE_RATE(YESTERDAY(),'USD','GBP')

where YESTERDAY() is a date function.
INDEXOF INDEXOF(array, findText)

Searches the array for the specified item, and returns its position.

Returns -1 if the item isn’t found.

If the item is present more than once, the INDEXOF function returns the position of the first occurrence.

For example, INDEXOF(csv['row'], 'Campaign ID') == 0 returns true if 'Campaign ID' was found in the first cell.

Note: The first item has position 0, the second item has position 1, and so on.
ISEMPTY ISEMPTY(value) Returns if the value is empty or not.
LEFT LEFT(text, numOfChars) Returns the requested number of characters from the left.
LEN LEN(text) Returns the number of characters in the given text
LOWER LOWER(text) Converts all uppercase letters in a string to lower-case letters.
MATCH MATCH(valueToMatch, array) Searches for a specified item in an array of values, and then returns a boolean result. For example, MATCH(1,{1,2,3}) returns true because the value '1' exists in the array.
PROPER PROPER(text) Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
REPLACE REPLACE(SourceStr, pattern, targetStr) Replaces every match of a pattern (a regex expression) in sourceStr to targetStr. For example, REPLACE('All-The-Best', '-', ' ') returns 'All The Best'. In another example, REPLACE('Let It Be','\\s','-') returns 'Let-It-Be'.
RIGHT RIGHT(text, numOfChars) Returns the requested number of characters from the right.
SELECT SELECT(sourceStr, pattern) Selects the first match of the pattern (a regex expression) in the sourceStr. For example, SELECT('Hello B wow E Goodbye', 'B(.*?)E') returns 'B wow E'.
SEMTIMEN SENTIMEN(text) Extracts a positive, neutral, or negative sentiment from the given text.
SPLIT SPLIT(text, delimiter) Splits a text string into an array by using a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string. For example, SPLIT('a,b,c', ',') returns an array with three items.
SUBSTITUTE SUBSTITUTE(text,oldText,newText) Substitutes newText for oldText in a text string. Use SUBSTITUTE when you want to replace specific text in a text string.
SUBSTRING SUBSTRING(text,beginIndex,endIndex) Returns the substring of the text beginning at beginIndex (inclusive) and ending at endIndex (exclusive).
TRIM TRIM(text) Removes all spaces from the text except for single spaces between words.
UPPER UPPER(text) Converts all lowercase letters in a text string to uppercase.
URL_DECODE URL_DECODE(text) Decodes an application/x-www-form-urlencoded string using a UTF-8 encoding scheme.
URL_ENCODE URL_ENCODE(text) Translates a text into an application/x-www-form-urlencoded format using a UTF-8 encoding scheme.
VALUE_FROM_JSON VALUE_FROM_JSON(JSON string, key) Returns the value for a given key in a JSON string. If the key doesn’t exist or the JSON is empty, an empty string is returned.
## Date Functions | FUNCTION NAME | SYNTAX | DESCRIPTION | | --- | --- | --- | | DATE | DATE(year, month) | Returns the first day of the given month in the given year. | | DATE | DATE(year, month, day) | Returns the date representing the given arguments. | | DATEADD | DATEADD(interval, number, date) | Returns the date after a specific time or date interval was added. The interval must be one of 'yyyy' (year), 'q' (quarter), 'm' (month), 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), or 's' (second). | | DATEDIFF | DATEDIFF(interval,startDate,endDate | Returns the number of time units specified by the interval between the startDate and the endDate. The startDate and endDate arguments must be date objects or date strings in standard form. The interval must be one of 'yyyy' (year), 'q' (quarter), 'm' (month), 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), or 's' (second). | | DAYPRECISION | DAYPRECISION(date) | Returns the given date with day precision, meaning the hours, minutes, and seconds fields are all set to 0. For example, the date '12-Jun-2012 16:34' is converted to '12-Jun-2012 00:00'. | | DAYSBETWEEN | DAYSBETWEEN(startDate, endDate) | Returns the number of days between the given startDate and endDate. The arguments must be date objects or date strings in standard form. | | DAYSINMONTH | DAYSINMONTH(date) | Returns the number of days in a specified month. | | NETWORKDAYS | NETWORKDAYS(startDate, endDate) | Returns the number of workdays between two dates, excluding weekends. The arguments must be date objects or date strings in standard form. | | NOW | NOW() | Returns the current system date and time. | | SETHOUR | SETHOUR(date, hour) | Returns the given date set with the given hours, with the hours being a numeric value from 0 through 23. | | TODAY | TODAY() | Returns the current system date. | | TOSECONDS | TOSECONDS(duration) | Returns the number of seconds represented by the given duration string, with the format HH:mm:ss/HH:mm:ss.SSS. | | YESTERDAY | YESTERDAY() | Returns yesterday’s system date. | ## Aggregation Functions | FUNCTION NAME | SYNTAX | DESCRIPTION | | --- | --- | --- | | GROUPCONCAT | GROUPCONCAT(groupedDimension, keyDimension, separator) | Concatenates the values of groupedDimension by keyDimension, with the separator string between each two grouped values. This process is done uniquely, that is, the same value repeated isn’t concatenated twice. For example, GROUPCONCAT({Media Buy Name}, {Campaign Name}, ',') results in all media buy names concatenated (with no repetitions) for each campaign. | ## Logical Functions | FUNCTION NAME | SYNTAX | DESCRIPTION | | --- | --- | --- | | AND | AND(condition1, condition2) | Logical AND, checks to see that both conditions are true. | | IF | IF(condition,resultifTrue,resultifFalse) | Returns the second argument if the first argument is true, and the third argument if otherwise. | | IFERROR | IFERROR(someExpression, number) | Returns the someExpression if it’s a valid number, otherwise returns a number. | | NOT | NOT(condition) | Logical NOT, reverses the logic of its argument, true becomes false and vice versa. | | OR | OR(condition) | Logical OR, checks to see if either one of the conditions is true. | ## Type Conversion Functions | FUNCTION NAME | SYNTAX | DESCRIPTION | | --- | --- | --- | | COMPRESS | COMPRESS(value1) | Compresses a string using gzip, and returns it in base64. | | DECODE\_BASE64 | DECODE\_BASE64(value1) | Decodes a string from base64. | | DOUBLE | DOUBLE(value1) | Converts a string to a double. | | ENCODE\_BASE64 | ENCODE\_BASE64(value1) | Encodes a string to base64. | | FORMATDATE | FORMATDATE(date, pattern) | Returns the string representation of the given date according to the supplied pattern. For example, 'yyyy-MM-dd' --> 2014-02-25, 'dd MMM-yyyy' --> 25 Feb-2014, etc. | | FORMATDATE\_DEFAULT | FORMATDATE\_DEFAULT(date) | Returns the default (yyyy-MM-dd HH:mm:ss.S) Marketing Cloud Intelligence string representation of the given date. | | MD5 | MD5(stringToHash) | Returns an MD5 hash of the given string. | | NUMBER | NUMBER(value1) | Converts a string to a number | | PARSEDATE | PARSEDATE(dateString, pattern) | Returns the date object specified by the given dateString using the given pattern. For example, '2014-02-25' ---> 'yyyy-MM-dd', '25 Feb-2014' ---> 'dd MMM-yyyy', etc. | | SHA256 | SHA256(stringToHash) | Returns a SHA256 hash of the given string. | | STRIP\_HTML | STRIP\_HTML(value1) | Strips HTML from a string. | | UNCOMPRESS | UNCOMPRESS(value1) | Uncompresses a base64 string using gzip. | ## Context Functions | FUNCTION NAME | SYNTAX | DESCRIPTION | | --- | --- | --- | | CONTEXT\_DIMENSION\_CONTAINS | CONTEXT\_DIMENSIONS\_CONTAIN(dimension) | Returns a 'true' value if the given dimension is included in the query. | | CONTEXT\_DIMENSION\_EQUALS | CONTEXT\_DIMENSIONS\_EQUALS(dimension) | Returns a 'true' value if the given dimensions are exactly the context dimensions. Enter all the relevant dimensions, but the order of appearance isn’t important. | | FilterEndDate | FilterEndDate() | Returns the end date of the query filter. | | FilterStartDate | FilterStartDate() | Returns the start date of the query filter. | | GET\_ENV | GET\_ENV(variableName) | Returns the value for the given environment variable. Possible values for variableName are: WORKSPACE\_ID | | QUERY\_DIMENSIONS\_CONTAIN | QUERY\_DIMENSIONS\_CONTAIN(dimension) | Returns a 'true' value if the given dimension is one of the query dimensions. | | QUERY\_DIMENSIONS\_EQUAL | QUERY\_DIMENSIONS\_EQUAL(\[dimensions\]) | Returns a 'true' value if the given dimensions are exactly the query dimensions. Enter all the relevant dimensions, but the order of appearance isn’t important. |