Function index

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).

ArrayAggIdentifies non-null row values in a column or group and aggregates them into a single array.
ArrayAggDistinctIdentifies distinct non-null row values in a column or group and aggregates them into a single array.
AvgCalculates the average value of a column or group.
AvgIfCalculates the average value of a column or group when the specified condition is True.
CorrCalculates the Pearson correlation coefficient (bivariate correlation) of two columns.
CountCounts the number of non-null and non-empty values in a column or group.
CountDistinctCounts the number of unique non-null and non-empty values in a column or group. Does not count duplicate values. (Same as Ndv.)
CountDistinctIfCounts 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.
CountIfCounts the number of non-null and non-empty values in a column or group when the specified condition is True.
GrandTotalCalculates the grand total for column or group.
ListAggJoins the values of a group or column into a single text string.
ListAggDistinctJoins the unique values of a group or column into a single text string. Does not include duplicate values.
MaxRetrieves the maximum (largest or latest) value in a column or group.
MaxIfRetrieves the maximum (largest or latest) value in a column or group when the specified condition is True.
MedianDetermines the median (midpoint) value of a column or group.
MinRetrieves the minimum (smallest or earliest) value in a column or group.
MinIfRetrieves the minimum (smallest or earliest) value in a column or group when the specified condition is True.
PercentileContCalculates the continuous kth percentile of a column or group.
PercentileDiscCalculates the discrete kth percentile of a column or group.
PercentOfTotalCalculates the percentage a value contributes to the specified aggregate total.
StdDevCalculates the standard deviation of a column or group.
SubtotalCalculates the subtotal of a column or group.
SumCalculates the sum of a column or group.
SumIfCalculates the sum of a column or group when the specified condition is True.
SumProductCalculates the product of row values across specified columns, then calculates the sum of the resulting products for a column or group.
VarianceEstimates the sample variance (spread of distribution) of a column or group.
VariancePopCalculates the population variance (spread of distribution) of a column or group.

Array functions

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

ArrayReturns an array containing specified values.
ArrayContainsSearches for a specific value in an array. If the value is found, the function returns True, otherwise it returns False.
ArrayDistinctReturns the array without duplicate values.
ArrayIntersectionCompares two arrays and returns an array of all overlapping elements, without duplicates.
ArrayJoinJoins elements of an array into a single text string.
ArrayLengthDetermines the number of entries in an array, or list.
ArraySliceReturns a portion of an array, defined by the starting index and length.
SequenceReturns an arithmetic sequence as an array of integers based on a specified range and increment

The following aggregate functions also create arrays:

ArrayAggIdentifies non-null row values in a column or group and aggregates them into a single array.
ArrayAggDistinctIdentifies 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.

ConvertTimezoneConverts date and time values to the specified time zone.
DateAddAdds a specified quantity of time to a date.
DateDiffCalculates the time difference between two dates.
DateFormatFormats a date value to text based on the format provided.
DateFromUnixConverts a Unix timestamp to a date value.
DateLookbackReturns the value of a variable at a previous point in time (or lookback period) determined by a specified date and offset.
DatePartExtracts the specified date part from a date value.
DateParseParses a text value in a specified format and returns a datetime value (date data type) in ISO format.
DateTimeConverts a text or date value into an datetime value in ISO format.
DateTruncTruncates the date to the specified date part.
DayReturns the day of the month from a date value as a number.
EndOfMonthReturns the last day of the month from a date value.
HourReturns the hour component from a date value as a number.
InDateRangeDetermines if a date falls within a specified date range and returns True or False.
InPriorDateRangeDetermines if a date falls within the date range of a prior period and returns True or False.
LastDayEvaluates a specified component in a date value and returns the last datetime value of that component in ISO format.
MakeDateEvaluates specified values representing year, month, and day units and returns a datetime value in ISO format.
MinuteReturns an integer representing the minute component in a specified datetime value.
MonthReturns an integer representing the month component in a specified datetime value.
MonthNameReturns the name of the month component from a specified datetime value.
NowReturns the current date and time using your organization's account timezone.
QuarterReturns an integer representing the quarter component in a specified datetime value.
SecondReturns an integer representing the second component in a specified datetime value.
TodayReturns the current date using your organization's account timezone.
WeekdayReturns an integer representing the day of the week in a specified datetime value.
WeekdayNameReturns the name of the day of the week in a specified datetime value.
YearReturns 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.

CAGRReturns the compound annual growth rate of an investment.
EffectReturns the effective annual interest rate.
FVReturns the future value of an investment.
IPmtReturns 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.
NominalReturns the nominal annual interest rate.
NPerReturns the number of periods for a loan or investment based on known amount, interest rate, and periodic payment amount.
PmtReturns the periodic payment for a loan based on the amount, number of periods, and constant interest rate.
PPmtReturns 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.
PVReturns the present value of a loan or an investment, when using constant and regular periodic payments.
XNPVReturns the net present value of an investment for payments or incomes at irregular intervals.

Geography functions

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.

📘

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.

AreaCalculates the area of a geography, in specified units.
CentroidCalculates the geographic center of a geography.
DistanceCalculates the minimum distance between two geographies, in specified units.
GeographyConverts GeoJSON or WKT formats to the geography data type.
IntersectsDetermines if one geography intersects another geography.
JsonConverts geography data to GeoJSON.
LatitudeReturns the latitude component of a point.
LongitudeReturns the longitude component of a point.
MakeLineConstructs a line from a series of points and line segments.
MakePointConstructs a point from latitude and longitude data.
PerimeterCalculates the perimeter of a geography, in specified units.
TextConverts geography data to WKT format.
WithinDetermines 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.

LookupFinds a value in a specified target element column and returns the corresponding row value from another column in that element.
RollupFinds 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.

BetweenDetermines if a value is within the specified range; True or False.
ChooseGiven a specified index number, returns the matching value from a list.
CoalesceReturns the first non-Null value from a list.
IfEvaluates if one or more conditions are true or false and returns the corresponding value.
InDetermines if a specified value matches any candidate values; True or False.
IsNotNullDetermines if the cell has a value; True or False.
IsNullDetermines if the cell is Null; True or False.
SwitchUsing the SWITCH paradigm, tests the specified value against a list of conditions, and returns the matching response.
ZnReturns 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.

AbsReturns the absolute value of a number.
AcosReturns the arccosine of an angle.
AsinReturns the arcsine of an angle.
AtanReturns the arctangent of an angle.
Atan2Returns the arctangent of a coordinate pair.
BinFixedCalculates the bin of a value among the specified number of identically-sized bins.
BinRangeCalculates the bin for a value using the specified lower bounds.
BitAndCalculates the bitwise AND of two numbers.
BitOrCalculates the bitwise OR of two numbers.
CeilingRounds the number up to the closest multiple of equal or greater value.
CosReturns the cosine of an angle.
CotReturns the cotangent of an angle.
DegreesConverts the angle measurement from radians to degrees.
DistanceGlobeCalculates the distance between two points on the globe, in kilometers.
DistancePlaneCalculates the distance between two points on a plane.
DivReturns the integer component of a division.
ExpReturns the mathematical constant e, or 2.71828.
FloorRounds the number down to the closest multiple of equal or lesser value.
GreatestReturns the largest value from a list.
IntRounds the integer down to the largest integer of lesser or equal value.
LeastReturns the smallest value from a list.
LnCalculates the natural logarithm of a number, log_e(n).
LogCalculates the logarithm of a number. Defaults to log_10(n).
ModReturns the remainder component of a division.
MRoundRounds the number down to the closest multiple of the specified number.
PiReturns the mathematical constant π, or 3.14159....
PowerCalculates the result of a number raised to the specified power.
RadiansConverts the angle measurement from degrees to radians.
RoundCalculates the number to the specified number of digits.
RoundDownRounds a number down to the specified number of digits or decimal places.
RoundUpRounds a number up to the specified number of digits or decimal places.
RowAvgCalculates the average value of a list of numbers.
SignCalculates the sign of a number. Returns -1 if negative, 1 if positive, or 0 if zero.
SinCalculates the sine of an angle.
SqrtCalculates the square root of a number.
TanCalculates the tangent of an angle.
TruncTruncates 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.

AggDatetimeCalls a data platform aggregate function that returns a date data type. Aggregate version of CallDatetime.
AggGeographyCalls a data platform aggregate function that returns a geography data type. Aggregate version of CallGeography.
AggLogicalCalls a data platform aggregate function that returns a logical data type. Aggregate version of CallLogical.
AggNumberCalls a data platform aggregate function that returns a number data type. Aggregate version of CallNumber.
AggTextCalls a data platform aggregate function that returns a text data type. Aggregate version of CallText.
AggVariantCalls a data platform aggregate function that returns a variant data type. Aggregate version of CallVariant.
CallDatetimeCalls a data platform function that returns a date data type.
CallGeographyCalls a data platform function that returns a geography data type.
CallLogicalCalls a data platform function that returns a logical data type.
CallNumberCalls a data platform function that returns a number data type.
CallTextCalls a data platform function that returns a text data type.
CallVariantCalls 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.

CurrentTimezoneReturns your organization's IANA time zone as configured in the Administration portal.
CurrentUserAttributeTextReturns the value of a specific attribute for the current (signed-in) user.
CurrentUserEmailReturns the email address associated with the current (signed-in) user's account.
CurrentUserFirstNameReturns the current (signed-in) user’s first name as configured in the user’s profile.
CurrentUserFullNameReturns the current (signed-in) user’s first and last name as configured in the user’s profile.
CurrentUserInTeamReturns 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.

ConcatCombines multiple strings into a single text value.
ContainsSearches for a specified substring in a text value. If the substring is found, the function returns True, otherwise it returns False.
EndsWithDetermines 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.
FindReturns the index where it first finds the specified substring within a string. Returns 0 if not found.
ILikeReturns True if the string matches the pattern. Case insensitive.
LeftReturns the left portion of the string (the beginning), up to specified number of characters.
LenReturns the number of characters in a string, including spaces.
LikeReturns True if the string value matches the pattern. Case sensitive.
LPadSets the string to a desired length by adding or removing characters at the front. Uses an optional fill character or defaults to extra spaces.
LowerConverts a string to all lower case.
LTrimRemoves leading spaces from a string.
MD5Calculates the hash value of a string for the MD5 message-digest algorithm (hashing function).
MidReturns a substring from a string, defined by offset and length. Same as Substring.
ProperConverts text to proper case, capitalizing the first letter of each word.
RegexpExtractReturns the substring that matches a regular expression within a string.
RegexpMatchReturns True if a string matches a regular expression.
RegexpReplaceReturns a string for a pattern and replaces it with a specified string.
RepeatReturns the result of repeating the string a specified number of times.
ReplaceReplaces every instance of a specified string with a replacement string.
ReverseReverses the order of characters in a string.
RightReturns the right portion of a string (the end), up to the specified number of characters.
RPadSets the string to a desired length by adding or removing characters at the end. Uses an optional fill character, or defaults to extra spaces.
RTrimRemoves trailing spaces from the end of a string.
SHA256Transforms text input into a 256-bit hash value.
SplitPartSplits 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.
StartsWithDetermines if a string starts with the specified substring. Returns True or False.
SubstringReturns a substring from a string, defined by offset and length. Same as Mid.
TrimRemoves both leading and trailing spaces from a string.
UpperConverts a string to upper case (all capital letters).

Type functions

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

DateConverts text or number values to the date data type in ISO datetime format.
JSONConverts values to the variant data type in JSON format.
LogicalConverts values to the logical data type in boolean format (true or false).
NumberConverts values to the number data type.
TextConverts values to the text data type.
VariantConverts text values to the variant data type.

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

GeographyConverts 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.

CumulativeAvgCalculates the running average up to and including the current row.
CumulativeCorrCalculates the correlation coefficient between dependent and independent data columns up to and including the current row.
CumulativeCountCounts the number of non-null values up to and including the current row.
CumeDistCalculates the cumulative distribution of values relative to the current row value.
CumulativeMaxReturns the largest value up to and including the current row.
CumulativeMinReturns the smallest value up to and including the current row.
CumulativeStdDevCalculates the standard deviation of values up to and including the current row.
CumulativeSumCalculates the sum of values up to and including the current row.
CumulativeVarianceCalculates 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.

MovingAvgCalculates the numerical average of a column within a moving window.
MovingCorrCounts the number of non-Null values in a moving window.
MovingCountCalculates the correlation coefficient of two numerical columns within a moving window. See Pearson (bivariate) correlation coefficient.
MovingMaxFinds the maximum value of a column within a moving window.
MovingMinFinds the minimum value of a column within a moving window.
MovingStdDevCalculates the standard deviation of a column within a moving window.
MovingSumCalculates the sum of a column in a moving window.
MovingVarianceCalculates 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.

FillDownReplaces all null values in a column or grouping with the closest prior non-null value.
FirstReturns the first row value of a column or grouping.
FirstNonNullReturns the first non-null value from a column or grouping.
LagReturns the value from a preceding offset row in a column or grouping.
LastReturns the last row value in a column or grouping.
LastNonNullReturns the last non-null value in a column or grouping.
LeadReturns the value from a subsequent offset row in a column or grouping.
NthReturns 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.

NtileAssigns the specified rank, in order, to the column rows of a column, approximately equal number of rows for each rank.
RankAssigns ranks to unique values in a column, from rank 1 onwards. Skips duplicate values.
RankDenseAssigns ranks to all values in a column, from rank 1 onwards. Assigns the same rank to duplicate values.
RankPercentileRanks the rows in the table by percentile.
RowNumberNumbers the table rows, starting with 1.