Formula function reference
ThoughtSpot allows you to create derived columns in Worksheets using formulas. You create these columns by building formulas using the Formula Assistant. An individual formula is constructed from n combination of operators and functions.
This reference lists the various operators and functions you can use to create formulas.
Operators
Operator  Description  Examples  


Returns 



Conditional operator. Accepts multiple clauses 



Returns the first value if it is not 



Takes a column name and a list of values. It checks each column value against the list of values in the formula, and returns 



Returns 



Returns 



Takes a column name and a list of values. It checks each column value against the list of values in the formula, and returns 



Returns 

Aggregate functions (group aggregate)
Use the following functions to aggregate data.
Function  Description  Examples 


Returns the average of all the values of a column. 


Returns the average of all the columns that meet a given criteria. 


Returns the number of rows in the table containing the column. 


Returns the number of rows in the table containing the column. 


Takes a measure and one or more attributes. Returns the average of the measure, accumulated by the attribute(s) in the order specified. 


Takes a measure and one or more attributes. Returns the maximum of the measure, accumulated by the attribute(s) in the order specified. 


Takes a measure and one or more attributes. Returns the minimum of the measure, accumulated by the attribute(s) in the order specified. 


Takes a measure and one or more attributes. Returns the sum of the measure, accumulated by the attribute(s) in the order specified. 


Takes a measure and optional attributes and filters. Used to specify columns and filters to include or ignore in your query. Commonly used in comparison analysis. 


Takes a measure and one or more attributes. Returns the average of the measure grouped by the attribute(s). 


Takes a measure and one or more attributes. Returns the count of the measure grouped by the attribute(s). 


Takes a measure and one or more attributes. Returns the maximum of the measure grouped by the attribute(s). 


Takes a measure and one or more attributes. Returns the minimum of the measure grouped by the attribute(s). 


Takes a measure and one or more attributes. Returns the standard deviation of the measure grouped by the attribute(s). 


Takes a measure and one or more attributes. Returns the sum of the measure grouped by the attribute(s). 


Takes a measure and one or more attributes. Returns the unique count of the measure grouped by the attribute(s). 


Takes a measure and one or more attributes. Returns the variance of the measure grouped by the attribute(s). 


Returns the maximum value of a column. 


Returns the maximum value among columns that meet a criteria. 


Returns the value of the measure from the row that has the 50th percentile value. 


Returns the minimum value of a column. 


Returns the minimum value among columns that meet a criteria. 


Takes a measure, two integers to define the window to aggregate over, and one or more attributes. The window is (current  Num1…Current + Num2) with both end points being included in the window. For example, “1,1” will have a window size of 3. To define a window that begins before Current, specify a negative number for Num2. Returns the average of the measure over the given window. The attributes are the ordering columns used to compute the moving average. 


Takes a measure, two integers to define the window to aggregate over, and one or more attributes. The window is (current  Num1…Current + Num2) with both end points being included in the window. For example, “1,1” will have a window size of 3. To define a window that begins before Current, specify a negative number for Num2. Returns the maximum of the measure over the given window. The attributes are the ordering columns used to compute the moving maximum. 


Takes a measure, two integers to define the window to aggregate over, and one or more attributes. The window is (current  Num1…Current + Num2) with both end points being included in the window. For example, “1,1” will have a window size of 3. To define a window that begins before Current, specify a negative number for Num2. Returns the minimum of the measure over the given window. The attributes are the ordering columns used to compute the moving minimum. 


Takes a measure, two integers to define the window to aggregate over, and one or more attributes. The window is (current  Num1…Current + Num2) with both end points being included in the window. For example, “1,1” will have a window size of 3. To define a window that begins before Current, specify a negative number for Num2. Returns the sum of the measure over the given window. The attributes are the ordering columns used to compute the moving sum. 


Returns the value of the measure from the row that has a 


Returns the rank for the current row. Identical values receive an identical rank. Takes an aggregate input for the first argument. The second argument specifies the order, 


Returns the percentile rank for the current row. Identical values are assigned an identical percentile rank. Takes an aggregate input for the first argument. The second argument specifies the order, 


Returns the standard deviation of all values of a column. 


Returns a standard deviation values filtered to meet a specific criteria. 


Returns the sum of all the values of a column. 


Returns sum values filtered by a specific criteria. 


Returns the number of unique values of a column. 


Returns the number of unique values of a column provided it meets a criteria. 


Returns the variance of all the values of a column. 


Returns the variance of all the values of a column provided it meets a criteria. 

Conversion functions
Use these functions to convert data from one data type into another data type.
ThoughtSpot does not support date data type conversion.
Function  Description  Examples 


Returns the input as a 


Accepts a date represented as an integer or text string, and a second string parameter that can include 


Returns the input as a 


Returns the input as an integer. 


Returns the input as a text string. To convert a date data type to a string data type, specify the date format you want to use. 

Date functions
Function  Description  Examples 


Returns the result of adding the specified number of days to the given date. 


Returns the result of adding the specified number of minutes to the given date/datetime/time. 


Returns the result of adding the specified number of months to the given date. 


Returns the result of adding the specified number of seconds to the given date/ datetime/ time. 


Returns the result of adding the specified number of weeks to the given date. 


Returns the result of adding the specified number of years to the given date. 


Returns the date portion of a given date. 


Returns the number (131) of the day of the month for the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, the 15th of the month is the start of the fiscal month.


Returns the number of the day in a quarter for a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the number (17) of the day in a week for a given date. Monday is 1, and Sunday is 7. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, Wednesday is the start of the fiscal week.


Returns the number (1366) of the day in a year from a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the day of the week for the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, the days of the week are in French in the fiscal calendar.


Subtracts the second date from the first date and returns the result in number of days. 


Subtracts the hour of the second date from the hour of the first date and returns the result in number of hours. 


Subtracts the minute of the second date from the minute of the first date and returns the result in number of minutes. 


Subtracts the month of the second date from the month of the first date and returns the result in number of months. The optional third parameter specifies the custom calendar the formula uses to calculate the result. 


Subtracts the quarter of the second date from the quarter of the first date and returns the result in number of quarters. The optional third parameter specifies the custom calendar the formula uses to calculate the result. 


Subtracts the second date from the first date and returns the result in number of seconds. 


Subtracts the week of the second date from the week of the first date and returns the result in number of weeks. The optional third parameter specifies the custom calendar the formula uses to calculate the result. 


Subtracts the year of the second date from the year of the first date and returns the result in number of years. The optional third parameter specifies the custom calendar the formula uses to calculate the result. 


Returns the hour of the day for the given date. 


Returns true if the given date falls on a Saturday or Sunday. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, Wednesday and Thursday are the days of the weekend in the fiscal week.


Returns the month from the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, the months of the year are in Spanish in the fiscal calendar.


Returns the number (112) of the month from a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the month (13) number for the given date in a quarter. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the current date and time in your locale’s standard date and time format. For example, if your locale is English (United States), it returns 


Returns the number (14) of the quarter associated with the given date. You can add an optional second parameter to specify fiscal or calendar dates. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns 
In the following example, the 15th is the start of the fiscal month.


Returns the date for the first day of the quarter for the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the date for the first day of the week for the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, Wednesday is the start of the fiscal week.


Returns the date for the first day of the year for the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the time portion of a given date. 


Returns the current date in your locale’s standard date format. For example, if your locale is English (United States), it returns 


Returns the week number for the given date in a month. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, the 15th is the start of the fiscal month.


Returns the week number for the given date in a quarter. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the week number for the given date in a year. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year.


Returns the year in integer format for a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year. Per standard convention, the fiscal year is defined by the yearend date.


Returns the year in string format for a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result. The default is calendar. 
In the following example, May 1st is the start of the fiscal year. Per standard convention, the fiscal year is defined by the yearend date.

Mixed functions
These functions can be used with text and numeric data types.
Function  Description  Examples 


Returns true if the first value is not equal to the second value. 


Returns true if the first value is less than the second value. 


Returns true if the first value is less than or equal to the second value. 


Returns true if the first value is equal to the second value. 


Returns true if the first value is greater than the second value. 


Returns true if the first value is greater than or equal to the second value. 


Returns the larger of the values. 


Returns the smaller of the values. 

Number functions
Function  Description  Examples 


Returns the result of multiplying both numbers. 


Returns the result of adding both numbers. 


Returns the result of subtracting the second number from the first. 


Returns the result of dividing the first number by the second. 


Returns the first number raised to the power of the second. 


Returns the absolute value. 


Returns the inverse cosine in degrees. 


Returns the inverse sine (specified in degrees). 


Returns the inverse tangent in degrees. 


Returns the inverse tangent in degrees. 


Returns the cube root of a number. 


Returns the smallest following integer. 


Returns the cosine of an angle (specified in degrees). 


Returns the cube of a number. 


Returns Euler’s number (~2.718) raised to a power. 


Returns 2 raised to a power. 


Returns the largest previous integer. 


Returns the natural logarithm. 


Returns the logarithm with base 10. 


Returns the logarithm with base 2 (binary logarithm). 


Returns the remainder of first number divided by the second number. 


Returns the first number raised to the power of the second number. 


Returns a random number between 0 and 1. 


Returns the first number rounded to the second number (the default is 1). 


Returns the result of dividing the first number by the second. If the second number is 0, returns 0 instead of NaN (not a number). 


Returns +1 if the number is greater than zero, 1 if less than zero, 0 if zero. 


Returns the sine of an angle (specified in degrees). 


Returns the distance in km between two points on Earth. 


Returns the square of a numeric value. 


Returns the square root. 


Returns the tangent of an angle (specified in degrees). 

Text functions
Function  Description  Examples 


Returns two or more values as a concatenated text string. Use single quotes around each literal string, not double quotes. 


Returns true if the first string contains the second string, otherwise returns false. 


Accepts two text strings. Returns the edit distance (minimum number of operations required to transform one string into the other) as an integer. Works with strings under 1023 characters. 


Accepts two text strings and an integer to specify the upper limit cap for the edit distance (minimum number of operations required to transform one string into the other). If the edit distance is less than or equal to the specified cap, returns the edit distance. If it is higher than the cap, returns the cap plus 1. Works with strings under 1023 characters. 


Returns the portion of the given string of given length, beginning from the left side of the string. 


Returns the portion of the given string of given length, beginning from the right side of the string. 


Accepts a document text string and a search text string. Returns true if relevance score (0100) of the search string with respect to the document is greater than or equal to 20. Relevance is based on edit distance, number of words in the query, and length of words in the query which are present in the document. 


Accepts a document text string and a search text string. Returns the relevance score (0100) of the search string with respect to the document. Relevance is based on edit distance, number of words in the query, and length of words in the query which are present in the document. If the two strings are an exact match, returns 100. 


Accepts two text strings. Returns true if they sound similar when spoken, and false if they do not. 


Accepts two text strings. Returns true if they are spelled similarly and false if they are not. Works with strings under 1023 characters. 


Returns the length of the text. 


Returns the numeric position (starting from 0) of the first occurrence of the second string in the first string, or 1 if not found. 


Returns the portion of the given string, beginning at the location specified (starting from 0), and of the given length. 

Connections passthrough functions
The following passthrough functions are only supported with Snowflake and Google BigQuery connections at this time. 
Function  Description  Examples 


Returns the boolean data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the boolean data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the date data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the date data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the timestamp data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the timestamp data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the double data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the double data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the int data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the int data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the timestamp data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the string data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 


Returns the time data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 
 

Returns the time data type. The first argument takes the signature of the external function to be executed against the datasource. Subsequent arguments take the values to be passed to the external function. 
