Functions

Functions for custom fields are powerful tools that allow you to enhance the flexibility and depth of your reports by creating and manipulating specialized data fields. These functions enable you to define and calculate values that are not directly available in your raw data, providing a way to tailor your reports to meet specific analytical needs and business requirements.

Analytics supports the following functions:

AVG IS NULL
CASE LEN
CHARINDEX LOWER
CONCAT MAX
CONCAT_WS MIN
COUNT MONTH
COUNT DISTINCT PATINDEX/POSITION
DATEADD REPLACE
DATEDIFF SUBSTRING
DAY SUM
GETDATE UPPER
IF YEAR

Conditional Functions

Conditional functions are built-in functions that return different values or perform different actions based on whether certain conditions are met. They are instrumental in creating custom calculations, formatting data based on specific criteria, and making data-driven decisions within your reports.

Checks the $condition and returns the expression of $true or $false accordingly. Multiple conditions are allowed.

Return type: mixed (depends on $true and $false type)

  • The return value of this function can be used as a parameter for any other function, as long as the types match. For example:

    AVG(CASE WHEN measure > 0 THEN measure1 ELSE measure2 END)

    In this case, if measure > 0, then it’s equivalent to AVG(measure1). For this reason, the validation of the $true and $false depends on the calling function (AVG in this example)

  • The following example results in an error because AVG does not accept another AVG or a dimension as a parameter::
    AVG(CASE WHEN measure > 0 THEN AVG(measure) ELSE dimension END)

Tips:
  • The return type of ALL $true and $false must match.
  • If there is no ELSE parameter and none of the WHEN conditions match, the function will return NULL.
  • If more than one WHEN condition matches, the function will return the result of the first matching condition.
  • When the return type is a dimension (string or date), it cannot have any aggregate function on any parameter of the expression.

Parameter values:

Parameter Accepted Types Examples
WHEN $condition A conditional expression (please check Comparison Operators)

Does not allow a field or expression without a comparison operator
Valid Invalid
CASE WHEN dimension = ‘My Text’ THEN measure ELSE 0 END CASE WHEN dimension = ‘My Text’ THEN measure ELSE ‘My Text’ END
CASE WHEN measure > 0 THEN ‘>' WHEN measure = 0 THEN ‘=’ ELSE '<’ END CASE WHEN measure THEN ‘>' WHEN measure + 1 THEN ‘=’ ELSE ‘<’ END
CASE WHEN 2 > 1 THEN AVG(measure) ELSE SUM(Measure) + 5 END CASE WHEN 2 > 1 THEN AVG(measure) = 1 ELSE SUM(Measure) > 5 END
CASE WHEN datefield > GETDATE() THEN UPPER(dimension) ELSE dimension END CASE WHEN datefield > ‘My Text’ THEN UPPER(dimension) ELSE dimension END
CASE WHEN COUNT(dimension) > 1 THEN 1 ELSE 0 END CASE WHEN COUNT(dimension) > 1 THEN ‘one’ ELSE 'two’ END
AVG(CASE WHEN measure > 0 THEN measure1 ELSE measure2 END) AVG(CASE WHEN measure > 0 THEN AVG(measure) ELSE dimension END)
MAX(CASE WHEN dimension != NULL THEN measure1 ELSE measure2 END) MAX(CASE WHEN 5+5 THEN measure ELSE datefield END)
THEN $true Any expression that is valid as a Custom Field expression

This parameter can be included more than once to match multiple conditions
ELSE $false Any expression that is valid as a Custom Field expression

This parameter is optional
Example:

This function:

Returns:

Checks the $condition and returns the expression of $true or $false accordingly. This function is similar to CASE WHEN, but has a more simpler usage, allowing only 1 condition and always else.

Return type: mixed (depends on $true and $false type)

The same rule of the CASE statement applies here regarding the return value of this function.

For example:

AVG(IF(measure>0, measure1, measure2)) is valid.

Check the CASE WHEN information panel for more details.

Tips:
  • The return type of $true and $false must match.
  • When the return type is a dimension (string or date), it cannot include any aggregate functions on any parameters of the expression.

Parameter values:

Parameter Accepted Type Examples
$condition A conditional expression (for more information, see Comparison Operators). Does not allow a field or expression without a comparison operator
Valid Invalid
IF(2>1, 2, 1) IF(2>1, 2, ‘1')
IF(dimension = ‘text’, AVG(measure), SUM(measure)) IF(dimension, MAX(measure), SUM(measure))
IF(dimension = ‘text’, AVG(measure), SUM(measure)) IF(measure > 1, MAX(measure), MAX(dimension))
IF(SUBSTRING(dimension, 0, 4) = MAX(dimension2), (1+2), 0) IF(datefield > ‘today’, datefield, ‘2022-01-01’)
IF(datefield > GETDATE(), datefield, ‘2022-01-01’) IF(2>1, IF(datefield>GETDATE(), dimension, 0), measure)
IF(2>1, IF(datefield>GETDATE(), measure, 0), measure) IF(datefield>IF(measure>0,2,1), datefield, 'NULL’)
IF(2>IF(measure>0,2,1), 1, NULL) IF(COUNT(dimension)>1, ‘true’, dimension)
IF(2>1, ‘true static value’, CONCAT(dimension, dimension2))  
AVG(IF(measure = NULL, measure1, measure2)) AVG(IF(measure = NULL, measure, dimension))
$true Any expression that is valid as a Custom Field expression
$false Any expression that is valid as a Custom Field expression
Example:

This function:

Returns:

Aggregation Functions

Aggregations functions can be used to summarize and condense large volumes of data, making it easier to interpret patterns, trends, and overall performance. Whether you’re analyzing sales figures, financial metrics, or operational statistics, aggregation functions provide a way to aggregate multiple data points into a single, meaningful value.

Returns the average of a measure $field, aggregated by the dimensions.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any measure

Does not allow other aggregate functions

Does not allow static values
Valid Invalid
AVG(measure) AVG(1)
  AVG(measure + measure)
  AVG(dimension)
  AVG(datefield)
  AVG(SUM(measure))
Example:

This function:

Returns:

Returns the number of the items found in the field.

Return type: Number

Parameter values:

Parameter Description
$field The field to count.
Example:

This function:

Returns:

Returns the count (or distinct count) of a dimension $field, aggregated by the dimensions.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
DISTINCT Optional flag to indicate to count unique values. DISTINCT
Valid Invalid

COUNT(DISTINCT dimension)

 COUNT(DISTINCT)
$field

The field that contains the value.

Any dimension, measure or date field

Does not allow other aggregate functions

Does not allow static values
Valid Invalid
COUNT(dimension) COUNT(1)
COUNT(datefield) COUNT(COUNT(measure))
COUNT(measure)  
Example:

This function:

Returns:

In this example, we have a list of last names and we want to know if they are unique users or if there are users with the same last name. The result shows two users with the last name Decker and 2 users with the last name Jones.

Returns the specified value IF the expression is NULL, otherwise returns the expression.

Return type: Dependent upon the selected parameters.

Parameter values:

Parameter Description
$expression The expression to test whether is NULL.
$replacement The value to return if expression is NULL.
Example:

This function:

Returns:

Returns the maximum value of a $field, aggregated by the dimensions on the widget. MAX will return:

  • For measures, the highest number on the set (MAX of 1 and 2 returns 2)
  • For dates, the highest date (MAX of 2022-01-01 and 2023-01-01 will return the latest)
  • For dimensions, the latest string on lexicographic order (MAX of ‘Earth’ and ‘Moon’ returns ‘Moon’)

Return type: The same as parameter field (number, string or date)

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any field

Does not allow other aggregate functions

Does not allow static values
Valid Invalid
MAX(measure) MAX(1)
MAX(dimension) MAX(measure + 1)
MAX(datefield) MAX(SUM(dimension))

Example:

This function:

Returns:

Returns the minimum value of a $field, aggregated by the dimensions on the widget. MIN Returns:

  • For measures, the lowest number on the set (MIN of 1 and 2 returns 1)
  • For dates, the lowest date (MIN of 2022-01-01 and 2023-01-01 will return the earliest)
  • For dimensions, the first string on lexicographic order (MIN of ‘Earth’ and ‘Moon’ returns ‘Earth’)

Return type: The same as parameter field (number, string or date)

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any field

Does not allow other aggregate functions

Does not allow static values
Valid Invalid
MIN(measure) MIN(1)
MIN(dimension) MIN(measure + 1)
MIN(datefield) MIN(SUM(dimension))
Example:

This function:

Returns:

Returns the sum value of a measure $field, aggregated by the dimensions on the widget.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any field

Does not allow other aggregate functions

Does not allow static values
Valid Invalid
SUM(measure) SUM(1)
  SUM(measure + measure)
  SUM(dimension)
  SUM(datefield)
  SUM(SUM(measure))

String Functions

String functions are designed to handle various text processing tasks, such as concatenation, extraction, replacement, and formatting. These functions are particularly valuable when dealing with custom fields, where the data may not always be in a standard format or may require specific modifications to meet business needs.

Searches for $substring inside $string and returns the position of the first character of that substring.

Return type: Number

Tips:
  • A starting point can be specified using the optional $start parameter, which indicates the character position.
  • Returns 0 (zero) if there is no substring.
  • The index of first character is 1. Searching for substring “abc” on string “abcde” will return 1.
  • A $start parameter less than 1 will still initiate the search from the beginning of the string.

Parameter values:

Parameter Description Accepted Types Examples
$substring The substring to be searched. This parameter must be in single quotes. Any dimension, date or static string
Valid Invalid
CHARINDEX('abc', ‘abcde’) CHARINDEX(1, ‘abcde’)
CHARINDEX(dimension, ‘abcde’, 2) CHARINDEX(measure, ‘abcde’, 1)
CHARINDEX('2022', datefield) CHARINDEX(2022, datefield)
CHARINDEX('abc', ‘abcde’, LEN(field)) CHARINDEX('abc', ‘abcde’, 'a')
$string The string from which the substring is to be searched. Any dimension, date or static string
$start

Optional: This parameter is only used when you need to specify a starting position in the string apart from the start of the string.

Note: You must delete this parameter if you are not using it.

 Any measure or static number
Example:

This function:

Returns:

Joins two or more strings, returning the result. Anything that is not a string will be casted as string.

Return type: String

Parameter values:

Parameter Description Accepted Types Examples
$field1 The fields to add together. Anything
Valid Invalid
CONCAT('Hello', ‘World’) CONCAT_WS(measure, Hello', ‘World’)
CONCAT('Hello', ‘World’, ‘How are you’, ‘doing’, ‘today?’) CONCAT_WS(AVG(measure), Hello', ‘World’)
CONCAT(dimension, datefield, measure, ‘string’, 2000)  
CONCAT(AVG(measure), '+', SUM(measure))  
$field2 The fields to add together. Anything
[…] Optional More parameters of any type
Example:

This function:

Returns:

Returns two or more strings with a separator. Anything that is not a string will be casted as string.

Return type: String

Parameter values:

Parameter Description Accepted Types Examples
$separator The separator to use.

Note: This parameter must be in single quotes.

 Any dimension, date field or static string
Valid Invalid
CONCAT_WS('', ‘Hello', ‘World’) CONCAT_WS(measure, Hello', ‘World’)
CONCAT_WS(’, ‘, 'Hello', ‘World’, ‘How are you’, ‘doing’, ‘today?’) CONCAT_WS(AVG(measure), Hello', ‘World’)
CONCAT_WS(’dimension, dimension, datefield, measure, ‘string’, 2000)  
CONCAT_WS(UPPER(dimension), AVG(measure), '+', SUM(measure))  
$field1 The first field to add together. Anything
$field2 The second field to add together. Anything
[…] Optional parameter. More parameters of any type
Example:

This function:

Returns:

Returns the amount of characters (the length) of a string. Anything that is not a string will be casted as string before length calculation. In scenarios like LEN(20+5), will calculate the formula first, so result is LEN(25) = 2.

Return Type: Number

Parameter values:

Parameter Description Accepted Types Valid Examples
$field The field that contains the value. Anything LEN(20)

LEN(20+5)

LEN('My text')

LEN(dimension)

LEN(measure)

LEN(datefield)

LEN(UPPER(dimension))

LEN(SUM(measure))
Example:

This function:

Returns:

Returns the same string passed as the parameter with all characters converted to lower case.

Return type: String

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any dimension or static string
Valid Invalid
LOWER('My text') LOWER(1)
LOWER(dimension) LOWER(measure)
LOWER(SUBSTRING('a', 'b', 1)) LOWER(datefield)
  LOWER(AVG(field))
Example:

This function:

Returns:

Searches for $pattern inside $field, and returns the position of the first character that matches the pattern. The pattern can include a regular expression. The search is case-insensitive. Returns 0 (zero) if do not find the pattern. The index of first character is 1. Searching for pattern “abc” on string “abcde” will return 1.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
$pattern The pattern to find. It MUST be surrounded by single quotes and %. Other wildcards can be used in pattern, such as:

  • % - Match any string of any length (including 0 length)

  • _ - Match one single character

  • [] - Match any characters in the brackets, e.g. [xyz]

  • [^] - Match any character not in the brackets, e.g. [^xyz]

You can only select non-numeric data fields.

 Static string
Input Function Result
POSITION('Text%', ‘My text') 0
POSITION('%Te_t%', 'My text') 4
POSITION(’%[^ 0-9A-Za-z]%', 'My text!') 8
POSITION(dimension, 'My text') invalid
POSITION(measure, 'My text') invalid
POSITION(UPPER(’Text’), 'My Test’) 0
$field The field to be searched. You can only select non-numeric data fields. Any dimension or static string

For example, these functions:

  1. Match any string of any length

  2. Match any characters in the brackets

return:

  1. Match '!m'

  2. Match 'c' or 'a'

Returns the same string passed as $field, but replacing all occurrences of $old by $new.

Return type: String

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any dimension or static string
Valid Invalid
REPLACE('AB', ‘A', 'XX’) REPLACE(100, 1, ‘XX’)
REPLACE('Field value is XXX', ‘XXX', dimension) REPLACE('Field value is XXX', ‘XXX', measure)
REPLACE(dimension, ‘1', '2’) REPLACE(dimension, 1, 2)
REPLACE(UPPER(dimension), dimension, LOWER(dimension)) REPLACE(AVG(measure), datefield, 'X’)
$old The string to be replaced. Any dimension or static string
$new The replacement string. Any dimension or static string
Example:

This function:

Returns:

Returns part of the string passed on $field, starting from character at position $start and ending after $length characters. If $start or $length is a float, the value will be ignore the decimal part.

Return type: String

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any dimension or static string
Valid Invalid
SUBSTRING('abcdef', 1, 5) SUBSTRING('abcdef', 1, -5)
SUBSTRING(dimension, 1, 5) SUBSTRING(measure, -1, 5)
SUBSTRING(UPPER(dimension), measure, LEN(dimension)) SUBSTRING(measure, UPPER(dimension), LEN(dimension))
$start

The start position. The first position in the string is 1.

Note: Must be a positive number.

 Any measure or number
$length

The number of characters to extract.

Note: Must be a positive number.

 Any measure or number
Example:

This function:

Returns:

Returns the same string passed as parameter, but with all characters converted to upper case.

Return type: String

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any dimension or static string
Valid Invalid
UPPER('My text') UPPER(1)
UPPER(dimension) UPPER(measure)
UPPER(SUBSTRING('a', 'b', 1)) UPPER(datefield)
  UPPER(AVG(field))
Example:

This function:

Returns:

Date Functions

Date functions in custom fields allow you to transform, analyze, and present time-based data in a way that enhances clarity and relevance. Whether you’re generating sales reports, tracking project timelines, or analyzing trends over time, incorporating date functions can help you perform calculations, filter data, and create dynamic visualizations.

Adds or subtracts a given date (specified by the $field parameter) by a specific amount (indicated by $number) of time (defined by $interval).

Return type: Date/Time

Parameter values:

Parameter Description Accepted Types Examples
$interval The type of interval to add. Can be one of the following values:
  • Year
  • Quarter
  • Month
  • Day of Year
  • Day Week
  • Week Day
  • Hour
  • Minute
  • Second
  • Millisecond
Any option of Date Interval data type
Valid Invalid
DATEADD(day, 1, datefield) DATEADD(day, 1, measure)
DATEADD(month, -measure, today) DATEADD(day, 1, dimension)
DATEADD(year, DATEDIFF(year, datefield, datefield), ‘2022-01-01 00:00:00’) DATEADD(day, 1, '2022-01-01a’)
DATEADD(hour, 12+1, DATEADD(datefield, day, 1, datefield)) DATEADD('day', 1, datefield)
DATEADD(day, 1, MAX(datefield)) DATEADD(day, dimension, ‘today’)
$number

The value of the time/date interval to add. Both positive and negative values are allowed.

Note: To extract, add the minus sign (-). For example, -5.

 Any number or measure
$field

The field that contains the value.

 Any date field, alias or static date
Example:

This function:

Returns:

Returns the difference in numbers (based on $interval) between two dates.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
$interval The type of interval to use. Can be one of the following values:
  • Year
  • Quarter
  • Month
  • Day of Year
  • Day Week
  • Week Day
  • Hour
  • Minute
  • Second
  • Millisecond
Any option of Date Interval data type
Valid Invalid
DATEDIFF(day, datefield, ‘2022-01-01’) DATEDIFF(day, measure, ‘01-01-2022’)
DATEDIFF(nanoseconds, today, datefield) DATEDIFF('nanoseconds', ‘today’, dimension)
DATEDIFF(day, MIN(datefield), MAX(datefield)) DATEDIFF(day, MIN(dimension), MAX(dimension))
$field1

The field that contains the date.

 Any date field, alias or static date
$field2 The field that contains the date. Any date field, alias or static date
Example:

This function:

Returns:

Returns the day of a given date. For example, for a date ‘2021-12-31’, it will return 31.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any date field

Does not allow date aliases
Valid Invalid
DAY(datefield) DAY(today)
DAY(GETDATE()) DAY(10)
DAY(DATEADD(day, 1, datefield)) DAY(measure)
DAY(MAX(datefield)) DAY(dimension)
DAY('2022-01-01') DAY(MONTH(datefield))
Example:

This function:

Returns:

Returns the current datetime based on database server configuration. Data returned is a datetime and can be used as any date field, to use in other functions like DATEADD, DATEDIFF, DAY and MAX. For example, to get the amount of days since year start, use:

DATEDIFF(DAY, CONCAT(YEAR(GETDATE()), '-01-01'), GETDATE())

Return type: Date

Parameter values:

No parameters.

Example:

This function:

Returns:

Returns the month of a given date. For example, ‘2021-12-31’, returns 12.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any date field

Does not allow date aliases
Valid Invalid
MONTH(datefield) MONTH(today)
MONTH(GETDATE()) MONTH(10)
MONTH(DATEADD(day, 1, datefield)) MONTH(measure)
MONTH(MAX(datefield)) MONTH(dimension)
MONTH('2022-01-01') MONTH(MONTH(datefield))
Example:

This function:

Returns:

Returns the year of a given date. For example, ‘2021-12-31’, returns 2021.

Return type: Number

Parameter values:

Parameter Description Accepted Types Examples
$field The field that contains the value. Any date field

Does not allow date aliases
Valid Invalid
YEAR(datefield) YEAR(today)
YEAR(GETDATE()) YEAR(10)
YEAR(DATEADD(day, 1, datefield)) YEAR(measure)
YEAR(MAX(datefield)) YEAR(dimension)
YEAR('2022-01-01') YEAR(MONTH(datefield))
Example:

This function:

Returns: