Sigma Computing Documentation

Sigma supports over 200 functions that enable you to perform simple and complex calculations, transformations, and extractions to get the most out of your data.

Browse this function index by category to learn more about the specific types of functions available to you.

Aggregate functions

Aggregate functions evaluate multiple rows of data to return a single value. For example, you can use aggregate functions to perform group calculations (like Sum and Avg), retrieve specific values (like Min and Max), assess the data to provide insights (like Count and CountDistinct), or join multiple values (like ArrayAgg and ListAgg).

Function	Description
ArrayAgg	Identifies non-null row values in a column or group and aggregates them into a single array.
ArrayAggDistinct	Identifies distinct non-null row values in a column or group and aggregates them into a single array.
Avg	Calculates the average value of a column or group.
AvgIf	Calculates the average value of a column or group when the specified condition is `True`.
Corr	Calculates the Pearson correlation coefficient (bivariate correlation) of two columns.
Count	Counts the number of non-null and non-empty values in a column or group.
CountDistinct	Counts the number of unique non-null and non-empty values in a column or group. Does not count duplicate values. (Same as Ndv.)
CountDistinctIf	Counts the number of unique non-null and non-empty values in a column or group when the specified condition is `True`. Does not count duplicate values.
CountIf	Counts the number of non-null and non-empty values in a column or group when the specified condition is `True`.
GrandTotal	Calculates the grand total for column or group.
ListAgg	Joins the values of a group or column into a single text string.
ListAggDistinct	Joins the unique values of a group or column into a single text string. Does not include duplicate values.
Max	Retrieves the maximum (largest or latest) value in a column or group.
MaxIf	Retrieves the maximum (largest or latest) value in a column or group when the specified condition is `True`.
Median	Determines the median (midpoint) value of a column or group.
Min	Retrieves the minimum (smallest or earliest) value in a column or group.
MinIf	Retrieves the minimum (smallest or earliest) value in a column or group when the specified condition is `True`.
PercentileCont	Calculates the continuous k^th percentile of a column or group.
PercentileDisc	Calculates the discrete k^th percentile of a column or group.
PercentOfTotal	Calculates the percentage a value contributes to the specified aggregate total.
RegressionIntercept	Calculates the y-intercept of the linear regression line.
RegressionR2	Calculates the coefficient of determination of the linear regression line.
RegressionSlope	Calculates the slope of the linear regression line.
StdDev	Calculates the standard deviation of a column or group.
Subtotal	Calculates the subtotal of a column or group.
Sum	Calculates the sum of a column or group.
SumIf	Calculates the sum of a column or group when the specified condition is `True`.
SumProduct	Calculates the product of row values across specified columns, then calculates the sum of the resulting products for a column or group.
Variance	Estimates the sample variance (spread of distribution) of a column or group.
VariancePop	Calculates the population variance (spread of distribution) of a column or group.

Array functions

Array functions create, manage, and manipulate arrays (lists of indexed values).

Function	Description
Array	Returns an array containing specified values.
ArrayCompact	Removes nulls from an array.
ArrayConcat	Combines multiple arrays into one array.
ArrayContains	Searches for a specific value in an array. If the value is found, the function returns `True`, otherwise it returns `False`.
ArrayDistinct	Returns the array without duplicate values.
ArrayExcept	Returns an array of all unique elements from one specified array not included in another specified array.
ArrayIntersection	Compares two arrays and returns an array of all overlapping elements, without duplicates.
ArrayJoin	Joins elements of an array into a single text string.
ArrayLength	Determines the number of entries in an array, or list.
ArraySlice	Returns a portion of an array, defined by the starting index and length.
RaggedHierarchy	Constructs a hierarchy with the values of the specified columns.
Sequence	Returns an arithmetic sequence as an array of integers based on a specified range and increment
SplitToArray	Splits a specified string by a given delimiter and returns an array of substrings.
Sparkline (Beta)	Generates sparkline charts from an array of JSON objects.
SparklineAgg (Beta)	Generates sparkline charts by aggregating values over time.

The following aggregate functions also create arrays:


ArrayAgg	Identifies non-null row values in a column or group and aggregates them into a single array.
ArrayAggDistinct	Identifies distinct non-null row values in a column or group and aggregates them into a single array.

Date functions

Date functions evaluate, convert, and manipulate date and time values.

Function	Description
ConvertTimezone	Converts date and time values to the specified time zone.
DateAdd	Adds a specified quantity of time to a date.
DateDiff	Calculates the time difference between two dates.
DateFormat	Formats a date value to text based on the format provided.
DateFromUnix	Converts a Unix timestamp to a date value.
DateFromUnixMs	Converts a Unix timestamp in milliseconds to a date value.
DateFromUnixUs	Converts a Unix timestamp in microseconds to a date value.
DateLookback	Returns the value of a variable at a previous point in time (or lookback period) determined by a specified date and offset.
DatePart	Extracts the specified date part from a date value.
DateParse	Parses a text value in a specified format and returns a datetime value (date data type) in ISO format.
DateTrunc	Truncates the date to the specified date part.
Day	Returns the day of the month from a date value as a number.
DayOfYear	Returns the day of the year from a date value as a number.
EndOfMonth	Returns the last day of the month from a date value.
Hour	Returns the hour component from a date value as a number.
InDateRange	Determines if a date falls within a specified date range and returns `True` or `False`.
InPriorDateRange	Determines if a date falls within the date range of a prior period and returns `True` or `False`.
LastDay	Evaluates a specified component in a date value and returns the last datetime value of that component in ISO format.
MakeDate	Evaluates specified values representing year, month, and day units and returns a datetime value in ISO format.
Minute	Returns an integer representing the minute component in a specified datetime value.
Month	Returns an integer representing the month component in a specified datetime value.
MonthName	Returns the name of the month component from a specified datetime value.
Now	Returns the current date and time using your organization’s account timezone.
Quarter	Returns an integer representing the quarter component in a specified datetime value.
Second	Returns an integer representing the second component in a specified datetime value.
Today	Returns the current date using your organization’s account timezone.
Weekday	Returns an integer representing the day of the week in a specified datetime value.
WeekdayName	Returns the name of the day of the week in a specified datetime value.
Year	Returns an integer representing the year component in a specified datetime value.

Financial functions

Financial functions evaluate and calculate data related to money, investments, interest rates, and other aspects of finance.

Function	Description
CAGR	Returns the compound annual growth rate of an investment.
Effect	Returns the effective annual interest rate.
FV	Returns the future value of an investment.
IPmt	Returns the interest portion of a periodic payment for a loan based on the amount, number of periods, and constant interest rate. The portion of the payment allocated to interest decreases over time.
Nominal	Returns the nominal annual interest rate.
NPer	Returns the number of periods for a loan or investment based on known amount, interest rate, and periodic payment amount.
Pmt	Returns the periodic payment for a loan based on the amount, number of periods, and constant interest rate.
PPmt	Returns the principal portion of a periodic payment for a loan based on the amount, number of periods, and constant interest rate. The portion of the payment allocated to principal increases over time.
PV	Returns the present value of a loan or an investment, when using constant and regular periodic payments.
XNPV	Returns the net present value of an investment for payments or incomes at irregular intervals.

Geography functions

Geography functions aren’t compatible with all data platform connections. To check if your connection supports these functions, see Supported data platforms and feature compatibility.

Geography functions enable you to work with the geography data type to analyze locations, routes, and other geospatial data. For example, you can use geography functions to transform data into compatible formats for geography maps.

Function	Description
Area	Calculates the area of a geography, in specified units.
Centroid	Calculates the geographic center of a geography.
Distance	Calculates the minimum distance between two geographies, in specified units.
Geography	Converts GeoJSON or WKT formats to the geography data type.
Intersects	Determines if one geography intersects another geography.
Json	Converts geography data to GeoJSON.
Latitude	Returns the latitude component of a point.
Longitude	Returns the longitude component of a point.
MakeLine	Constructs a line from a series of points and line segments.
MakePoint	Constructs a point from latitude and longitude data.
Perimeter	Calculates the perimeter of a geography, in specified units.
Text	Converts geography data to WKT format.
Within	Determines if one geography is fully within another geography.

Join functions

Join functions retrieve data from target elements based on related columns in the local and target elements.

Function	Description
Lookup	Finds a value in a specified target element column and returns the corresponding row value from another column in that element.
Rollup	Finds a value in a specified target element column and aggregates all corresponding row values from another column in that element.

Logical functions

Logical functions perform logical operations or evaluate conditional statements and typically return boolean (true or false) output.

Function	Description
Between	Determines if a value is within the specified range; `True` or `False`.
Choose	Given a specified index number, returns the matching value from a list.
Coalesce	Returns the first non-Null value from a list.
If	Evaluates if one or more conditions are `true` or `false` and returns the corresponding value.
In	Determines if a specified value matches any candidate values; `True` or `False`.
IsNotNull	Determines if the cell has a value; `True` or `False`.
IsNull	Determines if the cell is Null; `True` or `False`.
Switch	Using the SWITCH paradigm, tests the specified value against a list of conditions, and returns the matching response.
Zn	Returns non-Null values, or 0 (zero) instead of Null values.

Math functions

Math functions perform mathematical operations, including arithmetic, trigonometric, rounding, statistical, and logarithmic calculations.

Function	Description
Abs	Returns the absolute value of a number.
Acos	Returns the arccosine of an angle.
Asin	Returns the arcsine of an angle.
Atan	Returns the arctangent of an angle.
Atan2	Returns the arctangent of a coordinate pair.
BinFixed	Calculates the bin of a value among the specified number of identically-sized bins.
BinRange	Calculates the bin for a value using the specified lower bounds.
BitAnd	Calculates the bitwise `AND` of two numbers.
BitOr	Calculates the bitwise `OR` of two numbers.
Ceiling	Rounds the number up to the closest multiple of equal or greater value.
Cos	Returns the cosine of an angle.
Cot	Returns the cotangent of an angle.
Degrees	Converts the angle measurement from radians to degrees.
DistanceGlobe	Calculates the distance between two points on the globe, in kilometers.
DistancePlane	Calculates the distance between two points on a plane.
Div	Returns the integer component of a division.
Exp	Returns the mathematical constant `e`, or `2.71828`.
Floor	Rounds the number down to the closest multiple of equal or lesser value.
Greatest	Returns the largest value from a list.
Int	Rounds the integer down to the largest integer of lesser or equal value.
IsEven	Returns True if the integer part of a number is even.
IsOdd	Returns True if the integer part of a number is odd.
Least	Returns the smallest value from a list.
Ln	Calculates the natural logarithm of a number, `log_e(n)`.
Log	Calculates the logarithm of a number. Defaults to `log_10(n)`.
Mod	Returns the remainder component of a division.
MRound	Rounds the number down to the closest multiple of the specified number.
Pi	Returns the mathematical constant `π`, or `3.14159...`.
Power	Calculates the result of a number raised to the specified power.
Radians	Converts the angle measurement from degrees to radians.
Round	Calculates the number to the specified number of digits.
RoundDown	Rounds a number down to the specified number of digits or decimal places.
RoundUp	Rounds a number up to the specified number of digits or decimal places.
RowAvg	Calculates the average value of a list of numbers.
Sign	Calculates the sign of a number. Returns `-1` if negative, `1` if positive, or `0` if zero.
Sin	Calculates the sine of an angle.
Sqrt	Calculates the square root of a number.
Tan	Calculates the tangent of an angle.
Trunc	Truncates a number to the specified number of digits or decimal places.

Passthrough functions

Passthrough functions send requests to execute operations within your connected data platform, then return the response generated by your data platform’s native functions.

Function	Description
AggDatetime	Calls a data platform aggregate function that returns a date data type. Aggregate version of CallDatetime.
AggGeography	Calls a data platform aggregate function that returns a geography data type. Aggregate version of CallGeography.
AggLogical	Calls a data platform aggregate function that returns a logical data type. Aggregate version of CallLogical.
AggNumber	Calls a data platform aggregate function that returns a number data type. Aggregate version of CallNumber.
AggText	Calls a data platform aggregate function that returns a text data type. Aggregate version of CallText.
AggVariant	Calls a data platform aggregate function that returns a variant data type. Aggregate version of CallVariant.
CallDatetime	Calls a data platform function that returns a date data type.
CallGeography	Calls a data platform function that returns a geography data type.
CallLogical	Calls a data platform function that returns a logical data type.
CallNumber	Calls a data platform function that returns a number data type.
CallText	Calls a data platform function that returns a text data type.
CallVariant	Calls a data platform function that returns a variant data type.

System functions

System functions return information about your Sigma organization, including details about users and system configurations.

Function	Description
CurrentTimezone	Returns your organization’s IANA time zone as configured in the Administration portal.
CurrentUserAttributeText	Returns the value of a specific attribute for the current (signed-in) user.
CurrentUserEmail	Returns the email address associated with the current (signed-in) user’s account.
CurrentUserFirstName	Returns the current (signed-in) user’s first name as configured in the user’s profile.
CurrentUserFullName	Returns the current (signed-in) user’s first and last name as configured in the user’s profile.
CurrentUserInTeam	Returns `true` if the current user is a member of a specific teams.

Text functions

Text functions evaluate or manipulate string data to perform operations like text modification, formatting, and extraction.

Function	Description
Concat	Combines multiple strings into a single text value.
Contains	Searches for a specified substring in a text value. If the substring is found, the function returns `True`, otherwise it returns `False`.
EndsWith	Determines if a text value ends with a specified substring. If the substring is found at the end of the text value, the function returns `True`, otherwise it returns `False`.
Find	Returns the index where it first finds the specified substring within a string. Returns `0` if not found.
ILike	Returns `True` if the string matches the pattern. Case insensitive.
Left	Returns the left portion of the string (the beginning), up to specified number of characters.
Len	Returns the number of characters in a string, including spaces.
Like	Returns `True` if the string value matches the pattern. Case sensitive.
LPad	Sets the string to a desired length by adding or removing characters at the front. Uses an optional fill character or defaults to extra spaces.
Lower	Converts a string to all lower case.
LTrim	Removes leading spaces from a string.
MD5	Calculates the hash value of a string for the MD5 message-digest algorithm (hashing function).
Mid	Returns a substring from a string, defined by offset and length. Same as Substring.
Proper	Converts text to proper case, capitalizing the first letter of each word.
RegexpCount	Returns the number of times a regular expression pattern is matched in a string.
RegexpExtract	Returns the substring that matches a regular expression within a string.
RegexpMatch	Returns `True` if a string matches a regular expression.
RegexpReplace	Returns a string for a pattern and replaces it with a specified string.
Repeat	Returns the result of repeating the string a specified number of times.
Replace	Replaces every instance of a specified string with a replacement string.
Reverse	Reverses the order of characters in a string.
Right	Returns the right portion of a string (the end), up to the specified number of characters.
RPad	Sets the string to a desired length by adding or removing characters at the end. Uses an optional fill character, or defaults to extra spaces.
RTrim	Removes trailing spaces from the end of a string.
SHA256	Transforms text input into a 256-bit hash value.
SplitPart	Splits the string into multiple parts at the positions of each appearance of the delimiter and returns the nth part of the string at the specified position.
StartsWith	Determines if a string starts with the specified substring. Returns `True` or `False`.
Substring	Returns a substring from a string, defined by offset and length. Same as Mid.
Trim	Removes both leading and trailing spaces from a string.
Upper	Converts a string to upper case (all capital letters).
UrlPart	Extracts a component from a URL.

Type functions

Type functions perform type casting or type conversion operations that transform values from one data type to another.

Function	Description
Date	Converts text or number values to the date data type in ISO datetime format.
JSON	Converts values to the variant data type in JSON format.
Logical	Converts values to the logical data type in boolean format (`true` or `false`).
Number	Converts values to the number data type.
Text	Converts values to the text data type.
Variant	Converts text values to the variant data type.

The following geography function also transform values from one data type to another:


Geography	Converts GeoJSON or WKT formats to the geography data type.

Window functions

Window functions perform operations across an entire table, table grouping (grouped rows), or defined window of rows. Sigma supports cumulative, moving, shifting, and ranking window functions.

Cumulative window functions

Cumulative window functions evaluate a specified column in a table or grouping and return the running total or cumulative value for all rows up to and including the current row. This differs from aggregate values that calculate a summary value for the entire table or grouping.

Function	Description
CumulativeAvg	Calculates the running average up to and including the current row.
CumulativeCorr	Calculates the correlation coefficient between dependent and independent data columns up to and including the current row.
CumulativeCount	Counts the number of non-null values up to and including the current row.
CumeDist	Calculates the cumulative distribution of values relative to the current row value.
CumulativeMax	Returns the largest value up to and including the current row.
CumulativeMin	Returns the smallest value up to and including the current row.
CumulativeStdDev	Calculates the standard deviation of values up to and including the current row.
CumulativeSum	Calculates the sum of values up to and including the current row.
CumulativeVariance	Calculates the variance of a column up to and including the current row.

Moving window functions

Moving window functions evaluate a specified column and return a value based on a defined window of rows that moves in relation to the current row.


MovingAvg	Calculates the numerical average of a column within a moving window.
MovingCorr	Calculates the correlation coefficient of two numerical columns within a moving window. See Pearson (bivariate) correlation coefficient .
MovingCount	Counts the number of non-Null values in a moving window.
MovingMax	Finds the maximum value of a column within a moving window.
MovingMin	Finds the minimum value of a column within a moving window.
MovingStdDev	Calculates the standard deviation of a column within a moving window.
MovingSum	Calculates the sum of a column in a moving window.
MovingVariance	Calculates the statistical variance of a column in a moving window.

Shifting window functions

Shifting window functions evaluate a specified column in a table or grouping and return the value from a row that shifts in relation to the current row.


FillDown	Replaces all `null` values in a column or grouping with the closest prior non-null value.
First	Returns the first row value of a column or grouping.
FirstNonNull	Returns the first non-null value from a column or grouping.
Lag	Returns the value from a preceding offset row in a column or grouping.
Last	Returns the last row value in a column or grouping.
LastNonNull	Returns the last non-null value in a column or grouping.
Lead	Returns the value from a subsequent offset row in a column or grouping.
Nth	Returns the value from the nth row of a column or grouping.

Ranking window functions

Ranking window functions evaluate a specified column in a table or grouping and assign a rank to each row.


Ntile	Assigns the specified rank, in order, to the column rows of a column, approximately equal number of rows for each rank.
Rank	Assigns ranks to unique values in a column, from rank 1 onwards. Skips duplicate values.
RankDense	Assigns ranks to all values in a column, from rank 1 onwards. Assigns the same rank to duplicate values.
RankPercentile	Ranks the rows in the table by percentile.
RowNumber	Numbers the table rows, starting with 1.
VisibilityLimit	Limits the values displayed in a column to the number of specified values according to an order calculated by another ranking function.