-
-
-
-
-

## Aggregate Functions

Aggregate functions are special functions which summarize columns from lower levels. Aggregate functions can be used on on Grouped levels or in a Total column. For example, they can be used to Sum all of the values of a column, or Count all of the values for each grouping of a column. As such, aggregates must always refer to columns in a level lower than the result. If the column being aggregated is grouped, then the aggregate is computed for each group. For example, if a column[Revenue] is grouped by year, then Sum([Revenue]) computes the revenue for each year. The results of aggregate functions can be referenced by lower levels. An example would be to create a Sum([Amount]) column as Total and then an [Amount] / [Total] column as Percentage. This converts each Amount value into a relative percentage value.

Returns the absolute value of a number.
Computes the arccosine of the given angle.
Computes the arcsine of the given angle.
Computes the arctangent of the given angle.
Computes the arctangent of the given coordinates.
Calculate the average of a column of numbers.
Computes the bin for a value using the provided lower bounds.
Rounds the input up to the smallest integer that is of equal or greater value.
Returns the value with the corresponding index position. When an index number is provided and a corresponding value does not exist, a Null result is returned.
Returns the first non-Null value from the arguments provided.
Combine strings together.
Returns true if a string contains a substring.
Calculate the Pearson correlation coefficient, also known as the bivariate correlation, of two columns.
Computes the cosine of the given angle.
Computes the cotangent of the given angle.
Count the non-Null values within a column or group.
Counts the number of unique values within a column or group.
Counts the number of rows in a table or group for which all given conditions are true.
Compute the cumulative distribution of the input column. The cumulative distribution is a number between 0 and 1. The last row is always 1.
Calculates the numerical average of the column up to and including the current value.
Calculates the correlation coefficient of the column up to and including the current value.
Counts the number of non-null values up and including to the current value.
Calculates the maximum across the values up to and including the current value.
Calculates the minimum across the values up to and including the current value.
Calculates the standard deviation across the values up to and including the current value.
Calculates the sum across the values up to and including the current value.
Calculates the variance across the values up to and including the current value.
Convert the value into a date and time. All times are assumed to be UTC.
Adds the specified quantity of time to a date.
Calculates the time difference between two dates.
Convert a Unix-style timestamp (seconds since January 1, 1970) to a Date.
Extract the specified date part, e.g. “month”, from the given date.
Truncate the date to the specified date part, e.g.”month”.
Returns the day of the month of the given date as a number.
Converts an angle in radians to degrees.
Calculates the distance between two points on the globe, in kilometers.
Calculates the distance between two points on the plane.
Returns the integer component of a division operation.
Returns the integer component of a division operation.
Returns true if a string ends with a substring.
Returns the mathematical constant e (2.7183…) raised to the given power.
Replaces a column’s null values with the closest, prior non-null value.
Returns the index at which a substring is first found within a given string. If the substring is not found, the result is 0.
Repeats the first row value of the given column for every row in the output column.
Rounds the input down to the largest integer that is of equal or lesser value.
Returns the largest value amongst the inputs.
Returns the hour component of the given date as a number.
Returns the value provided the first condition that is met.
Test if a value matches any value in a subsequent list of values. Returns True if the first value matches any of the subsequent values. Returns False if no values are matched.
Returns True if the argument contains valid data, and False if the argument does not contain valid data (Null).
Returns True if the argument does not contain valid data (Null), and False otherwise.
Interpret the column as containing JSON.
Removes leading spaces from a given string.
Shift a column downward.
Repeat the last value of the given column for every row in a column.
Shift a column upward.
Returns the smallest value amongst the inputs.
Returns a substring beginning at the start of a given string.
Returns the number of characters in a string, including spaces.
Joins the values in a column into a single value. Each window of values within a column will be combined into a single list of values.
Compute the natural logarithm of a number.
Compute the logarithm of a number, defaulting to base 10.
Convert the value into True or False.
Converts a given string to lowercase.
Find the maximum value for a column or group.
Find the median of a column or group.
Returns a substring defined by offset and length.
Find the minimum value for a column or group.
Returns the minute component of the given date as a number.
Returns the month component of the given date as a number.
Calculates the numerical average in a moving window.
Calculates the correlation coefficient in a moving window.
Count values in a moving window. Null values are not counted.
Find the maximum value within a moving window.
Find the minimum value within a moving window.
Calculate the standard deviation within a moving window.
Sum the values in a moving window.
Calculates the statistical variance in a moving window.
Counts the number of unique values within a column or group. This is an alias of the CountDistinct function and works the same way.
Returns the current UTC date and time.
Repeat the nth value of the the given column for every row in a column.
Assign the rows of a column to a given number of ranks.
Convert the value into a number.
Calculate the continuous k-th percentile value for a column or group.
Find the discrete k-th percentile value for a column or group.
Returns the mathematical constant π = 3.14159…
Returns the quarter component of the given date, as a number.
Removes trailing spaces from the right side of a given string.
Converts an angle in degrees to radians.
Rank the rows in the table using the input column. Begins with 1. Identical values are assigned identical rank. Defaults to sort Ascending.
Rank the rows in the table using the input column. Begins with 1. Identical values are assigned identical rank, but no gaps are inserted into the number sequence. Defaults to sort ascending.
Rank the rows in the table by percentile using the input column.
Returns the substring that matches a regular expression within a string.
Returns true if a string matches a regular expression.
Searches a string for a pattern and replaces it.
Searches a string for a substring, and replaces it.
Reverses a string.
Returns a substring terminating at the end of a given string.
Round a number to the specified number of digits.
Number the rows in the table using the input column. Begins with 1.
Calculate the average of a column of numbers.
Calculate the Pearson correlation coefficient, also known as the bivariate correlation, of two columns.
Count the non-Null values within a column or group.
Counts the number of unique values within a column or group.
Counts the number of rows in a table or group for which all given conditions are true.
Find the maximum value for a column or group.
Find the median of a column or group.
Find the minimum value for a column or group.
Counts the number of unique values within a column or group. This is an alias of the CountDistinct function and works the same way.
Calculate the continuous k-th percentile value for a column or group.
Find the discrete k-th percentile value for a column or group.
Compute the standard deviation of a column or group.
Sum the values in a column or group.
Sum the values in a column if all the conditions are true.
Compute the statistical variance of a column or group.