Supported Functions

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.