Supported Functions

Several categories of functions are supported for calculated fields, including aggregate functions, conditional functions, date functions, numeric functions, mathematical functions, string functions, and table calculations.

Aggregate Functions

Aggregate functions return values grouped by the chosen dimension or dimensions. For each aggregation, there is also a conditional aggregation that performs the same type of aggregation based on a condition.

  • avg averages the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.
  • avgIf calculates the average based on a conditional statement.
  • count calculates the number of values in a dimension or measure, grouped by the chosen dimension or dimensions.
  • countIf calculates the count based on a conditional statement.
  • distinct_count calculates the number of distinct values in a dimension or measure, grouped by the chosen dimension or dimensions.
  • distinct_countIf calculates the distinct count based on a conditional statement.
  • max returns the maximum value of the specified measure, grouped by the chosen dimension or dimensions.
  • maxIf calculates the maximum based on a conditional statement.
  • median returns the median value of the specified measure, grouped by the chosen dimension or dimensions.
  • medianIf calculates the median based on a conditional statement.
  • min returns the minimum value of the specified measure, grouped by the chosen dimension or dimensions.
  • minIf calculates the minimum based on a conditional statement.
  • percentile (alias of percentileDisc) computes the nth percentile of the specified measure, grouped by the chosen dimension or dimensions.
  • percentileCont calculates the nth percentile based on a continuous distribution of the numbers of the specified measure, grouped by the chosen dimension or dimensions.
  • percentileDisc (percentile) calculates the nth percentile based on the actual numbers of the specified measure, grouped by the chosen dimension or dimensions.
  • periodToDateAvg averages the set of numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateCount calculates the number of values in a dimension or measure for a given time granularity, such as a quarter, up to a point in time including duplicates.
  • periodToDateMax returns the maximum value of the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateMedian returns the median value of the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateMin returns the minimum value of the specified measure or date for a given time granularity, such as a quarter, up to a point in time.
  • periodToDatePercentile calculates the percentile based on the actual numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDatePercentileCont calculates the percentile based on a continuous distribution of the numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateStDev calculates the standard deviation of the set of numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time based on a sample.
  • periodToDateStDevP calculates the population standard deviation of the set of numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time based on a sample.
  • periodToDateSum adds the set of numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateVar calculates the sample variance of the set of numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateVarP calculates the population variance of the set of numbers in the specified measure for a given time granularity, such as a quarter, up to a point in time.
  • stdev calculates the standard deviation of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a sample.
  • stdevIf calculates the sample standard deviation based on a conditional statement.
  • stdevp calculates the standard deviation of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a biased population.
  • stdevpIf calculates the population deviation based on a conditional statement.
  • sum adds the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.
  • sumIf calculates the sum based on a conditional statement.
  • var calculates the variance of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a sample.
  • varIf calculates the sample variance based on a conditional statement.
  • varp calculates the variance of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a biased population.
  • varpIf calculates the population variance based on a conditional statement.

When a calculated field formula contains an aggregation, it becomes a custom aggregation. To make sure that the data is displayed accurately, these rules apply to custom aggregations:

  • Custom aggregations cannot contain nested aggregate functions, as in the formula sum(avg(x)/avg(y)). However, you can nest nonaggregated functions inside or outside aggregate functions, as in the formulas ceil(avg(x)) or avg(ceil(x)).
  • Custom aggregations cannot contain both aggregated and nonaggregated fields, in any combination.
  • Filter groups cannot contain both aggregated and nonaggregated fields.
  • You cannot convert custom aggregations to a dimension or add them to a field well as a dimension.
  • In pivot tables, you cannot add custom aggregations to table calculations.
  • Scatter plots with custom aggregations must have at least one dimension in the Group/Color field well.

Conditional Functions

  • coalesce returns the value of the first argument that is not null.
  • ifelse evaluates a set of if, then expression pairings, and returns the value of the then argument for the first if argument that evaluates to TRUE.
  • in evaluates an expression to see if it is in a given list of values.
  • isNotNull evaluates an expression to see if it is not null.
  • isNull evaluates an expression to see if it is null. If the expression is null, the function returns TRUE, and otherwise it returns false.
  • notIn evaluates an expression to see if it is not in a given list of values.
  • nullIf compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression.
  • switch returns an expression that matches the first label equal to the condition expression.

Date Functions

  • addDateTime adds or subtracts a unit of time from the date or time provided.
  • addWorkDays adds or subtracts the given number of work days from the date or time provided.
  • dateDiff returns the difference in days between two date fields.
  • epochDate converts an epoch date into a standard date.
  • extract returns a specified portion of a date value.
  • formatDate formats a date using a pattern that you specify.
  • isWorkDay returns TRUE if a given date-time value is a work or business day.
  • netWorkDays returns the number of working days between the provided two date values.
  • now returns the current date and time.
  • truncDate returns a date value that represents a specified portion of a date.

Numeric Functions

  • ceil rounds a decimal value to the next highest integer.
  • decimalToInt converts a decimal value to an integer.
  • floor decrements a decimal value to the next lowest integer.
  • intToDecimal converts an integer value to a decimal.
  • round rounds a decimal value to the closest integer or, if scale is specified, to the closest decimal place.

Mathematical Functions

  • mod(number, divisor) finds the remainder after dividing a number by a divisor.
  • log(expression) returns the base 10 logarithm of a given expression.
  • ln(expression) returns the natural logarithm of a given expression.
  • abs(expression) returns the absolute value of a given expression.
  • sqrt(expression) returns the square root of a given expression.
  • exp(expression) returns the base of the natural logarithm (e) raised to the power of a given expression.

String Functions

  • concat concatenates two or more strings.
  • contains checks if an expression contains a substring.
  • endsWith checks if the expression ends with the substring specified.
  • left returns the specified number of leftmost characters from a string.
  • locate locates a substring within another string, and returns the number of characters before the substring.
  • ltrim removes the preceding blank space from a string.
  • parseDate parses a string to determine if it contains a date value and returns the date if it finds it.
  • parseDecimal parses a string to determine if it contains a decimal value.
  • parseInt parses a string to determine if it contains an integer value.
  • replace replaces part of a string with a new string.
  • right returns the specified number of rightmost characters from a string.
  • rtrim removes the following blank space from a string.
  • split splits a string into an array of substrings, based on a delimiter that you choose, and returns the item specified by the position.
  • startsWith checks if the expression starts with the substring specified.
  • strlen returns the number of characters in a string.
  • substring returns the specified number of characters in a string, starting at the specified location.
  • toString formats the input expression as a string.
  • trim removes both the preceding and following blank space from a string.

In this section:

  1. Supported Table Calculations
    Table calculations are a group of functions that provide context in an analysis and can help you create enriched aggregated analyses. A table calculation function performs operations on the data to reveal relationships between fields. For example, you can calculate percentage of total, running sum, difference, common baseline, and rank.
Parent topic: Supported Operators and Functions