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 

and 
Returns true when both conditions are true , otherwise returns false . 
(1 = 1) and (3 > 2) = true lastname = 'smith' and state ='texas'
Note: Not available for rowlevel security (RLS) formulas.

if…then…else 
Conditional operator  if (3 > 2) then 'bigger' else 'not bigger' if (cost > 500) then 'flag' else 'approve' 
ifnull 
Returns the first value if it is not null , otherwise returns the second value. 
ifnull (cost, 'unknown') 
isnull 
Returns true if the value is null . 
isnull (phone) 
not 
Returns true if the condition is false , otherwise returns false . 
not (3 > 2) = false not (state = 'texas') 
or 
Returns true when either condition is true , otherwise returns false . 
(1 = 5) or (3 > 2) = true state = 'california' or state ='oregon' 
Aggregate functions (group aggregate)
Use the following functions to aggregate data.
Function  Description  Examples 

average 
Returns the average of all the values of a column.  average (revenue) 
average_if 
Returns the average of all the columns that meet a given criteria.  average_if(city = "San Francisco", revenue) 
count 
Returns the number of rows in the table containing the column.  count (product) 
count_if 
Returns the number of rows in the table containing the column.  count_if(region =’west’, region) 
cumulative_average 
Takes a measure and one or more attributes. Returns the average of the measure, accumulated by the attribute(s) in the order specified.  cumulative_average (revenue, order date, state) 
cumulative_max 
Takes a measure and one or more attributes. Returns the maximum of the measure, accumulated by the attribute(s) in the order specified.  cumulative_max (revenue, state) 
cumulative_min 
Takes a measure and one or more attributes. Returns the minimum of the measure, accumulated by the attribute(s) in the order specified.  cumulative_min (revenue, campaign) 
cumulative_sum 
Takes a measure and one or more attributes. Returns the sum of the measure, accumulated by the attribute(s) in the order specified.  cumulative_sum (revenue, order date) 
group_aggregate 
Takes a measure and optional attributes and filters. Used
to aggregate measures with different granularities and filters than the columns used in the search. Commonly used in comparison
analysis.
This formula takes the following form: Define lists using curly brackets, 
group_aggregate (sum (revenue), {ship mode, date}, {} ) group_aggregate (sum (revenue), {ship mode , date}, {day_of_week (date) = 'friday'} ) group_aggregate (sum (revenue), query_groups(), query_filters() ) group_aggregate (sum (revenue), query_groups() + {date}, query_filters() )

group_average 
Takes a measure and one or more attributes. Returns the average of the measure grouped by the attribute(s).  group_average (revenue, customer region, state) 
group_count 
Takes a measure and one or more attributes. Returns the count of the measure grouped by the attribute(s).  group_count (revenue, customer region) 
group_max 
Takes a measure and one or more attributes. Returns the maximum of the measure grouped by the attribute(s).  group_max (revenue, customer region) 
group_min 
Takes a measure and one or more attributes. Returns the minimum of the measure grouped by the attribute(s).  group_min (revenue, customer region) 
group_stddev 
Takes a measure and one or more attributes. Returns the standard deviation of the measure grouped by the attribute(s).  group_stddev (revenue, customer region) 
group_sum 
Takes a measure and one or more attributes. Returns the sum of the measure grouped by the attribute(s).  group_sum (revenue, customer region) 
group_unique_count 
Takes a measure and one or more attributes. Returns the unique count of the measure grouped by the attribute(s).  group_unique_count (product , supplier) 
group_variance 
Takes a measure and one or more attributes. Returns the variance of the measure grouped by the attribute(s).  group_variance (revenue, customer region) 
max 
Returns the maximum value of a column.  max (sales) 
max_if 
Returns the maximum value among columns that meet a criteria.  max_if( (revenue > 10) , customer region ) 
min 
Returns the minimum value of a column.  min (revenue) 
min_if 
Returns the minimum value among columns that meet a criteria.  min_if( (revenue < 10) , customer region ) 
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 average of the measure over the given window. The attributes are the ordering columns used to compute the moving average.  moving_average (revenue, 2, 1, customer region) 
moving_max 
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.  moving_max (complaints, 1, 2, store name) 
moving_min 
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.  moving_min (defects, 3, 1, product) 
moving_sum 
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.  moving_sum (revenue, 1, 1, order date) 
rank 
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, 'asc'  'desc' . 
rank (sum (revenue) , 'asc' ) rank (sum (revenue) , ‘desc' )

rank_percentile 
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, 'asc'  'desc' . 
rank_percentile (sum (revenue) , 'asc' ) rank_percentile (sum (revenue) , 'desc' ) 
stddev 
Returns the standard deviation of all values of a column.  stddev (revenue) 
stddev_if 
Returns a standard deviation values filtered to meet a specific criteria.  stddev_if( (revenue > 10) , (revenue/10.0) ) 
sum 
Returns the sum of all the values of a column.  sum (revenue) 
sum_if 
Returns sum values filtered by a specific criteria.  sum_if(region=’west’, revenue) 
unique count 
Returns the number of unique values of a column.  unique count (customer) 
unique_count_if 
Returns the number of unique values of a column provided it meets a criteria.  unique_count_if( (revenue > 10) , order date ) 
variance 
Returns the variance of all the values of a column.  variance (revenue) 
variance_if 
Returns the variance of all the values of a column provided it meets a criteria..  variance_if( (revenue > 10) , (revenue/10.0) ) 
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 

to_bool 
Returns the input as a boolean data type (true or false ). 
to_bool (0) = false to_bool (married) 
to_date 
Accepts a date represented as an integer or text string, and a second string parameter that can include strptime date formatting elements.Replaces all the valid strptime date formatting elements with their string counterparts and returns the result. Does not accept epoch formatted dates as input. 
to_date (date_sold, '%Y%m%d') 
to_double 
Returns the input as a double data type. 
to_double ('3.14') = 3.14 to_double (revenue * .01) 
to_integer 
Returns the input as an integer.  to_integer ('45') + 1 = 46 to_integer (price + tax  cost) 
to_string 
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.  to_string (45 + 1) = '46'
to_string (revenue  cost)
to_string (date,('%m/%d/%y')) 
Date functions
Function  Description  Examples 

add_days 
Returns the result of adding the specified number of days to the given date.  add_days (01/30/2015, 5) = 02/04/2015 add_days (invoiced, 30) 
add_minutes 
Returns the result of adding the specified number of minutes to the given date/datetime/time.  add_minutes ( 01/30/2015 00:10:20 , 5 ) = 01/30/2015 00:15:20 add_minutes ( invoiced , 30 ) 
add_months 
Returns the result of adding the specified number of months to the given date.  add_months ( 01/30/2015, 5 ) = 06/30/2015 add_months ( invoiced_date , 5 ) 
add_seconds 
Returns the result of adding the specified number of seconds to the given date/ datetime/ time.  add_seconds ( 01/30/2015 00:00:00, 5 ) = 06/30/2015 00:00:05 add_seconds ( invoiced_date , 5 ) 
add_weeks 
Returns the result of adding the specified number of weeks to the given date.  add_weeks ( 01/30/2015, 2 ) = 02/13/2015 add_weeks ( invoiced_date , 2 ) 
add_years 
Returns the result of adding the specified number of years to the given date.  add_years ( 01/30/2015, 5 ) = 01/30/2020 add_years ( invoiced_date , 5 ) 
date 
Returns the date portion of a given date.  date (home visit) 
day 
Returns the number (131) of the day of the month for the given date.  day (01/15/2014) = 15 day (date ordered) 
day_number_of_quarter 
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'. 
day_number_of_quarter (01/30/2015) = 30 In the following example, May 1st is the start of the fiscal year. day_number_of_quarter (01/30/2015, 'fiscal') = 91 
day_number_of_week 
Returns the number (17) of the day in a week for a given date. Monday is 1, and Sunday is 7.  day_number_of_week(01/15/2014) = 3 day_number_of_week (shipped) 
day_number_of_year 
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'.  day_number_of_year (01/30/2015) = 30 In the following example, May 1st is the start of the fiscal year. day_number_of_year ( 01/30/2015, 'fiscal' ) = 275 day_number_of_year (invoiced) 
day_of_week 
Returns the day of the week for the given date.  day_of week (01/30/2015) = Friday day_of_week (serviced) 
diff_days 
Subtracts the second date from the first date and returns the result in number of days, rounded down if not exact.  diff_days (01/15/2014, 01/17/2014) = 2 diff_days (purchased, shipped) 
diff_time 
Subtracts the second date from the first date and returns the result in number of seconds.  diff_time (01/30/2014, 01/31/2014) = 86,400 diff_time (clicked, submitted) 
hour_of_day 
Returns the hour of the day for the given date.  hour_of_day (received) 
is_weekend 
Returns true if the given date falls on a Saturday or Sunday.  is_weekend (01/31/2015) = true is_weekend (emailed) 
month 
Returns the month from the given date.  month (01/15/2014) = January month (date ordered) 
month_number 
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'.  month_number (09/20/2014) = 9 In the following example, May 1st is the start of the fiscal year. month_number ( 09/20/2014, 'fiscal' ) = 5 month_number (purchased) 
month_number_of_quarter 
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'.  month_number_of_quarter (02/20/2018) = 2 In the following example, May 1st is the start of the fiscal year. month_number_of_quarter (02/20/2018,'fiscal' ) = 1 
now 
Returns the current timestamp.  now () 
quarter_number 
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'.  quarter_number ( 04/14/2014) = 2 In the following example, May 1st is the start of the fiscal year. quarter_number ( 04/14/2014, 'fiscal' ) = 4 quarter_number ( shipped ) 
start_of_month 
Returns MMM yyyy for the first day of the month. Your installation configuration can override this setting so that it returns a different format such as MM/dd/yyyy . Speak with your ThoughtSpot administrator for information on doing this. 
start_of_month ( 01/31/2015 ) = Jan FY 2015 start_of_month (shipped) 
start_of_quarter 
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'.  start_of_quarter ( 04/30/2014) = Apr 2014 In the following example, May 1st is the start of the fiscal year. start_of_quarter ( 04/30/2014, 'fiscal') = Feb 2014 start_of_quarter (sold) 
start_of_week 
Returns the date for the first day of the week for the given date.  start_of_week ( 01/31/2020 ) = 01/27/2020 start_of_week (emailed) 
start_of_year 
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'. 
start_of_year (04/30/2014) returns Jan 2014 In the following example, May 1st is the start of the fiscal year. start_of_year (04/30/2014, 'fiscal') returns May 2013 start_of_year (joined) 
time 
Returns the time portion of a given date.  time (1/31/2002 10:32) = 10:32 time (call began) 
today 
Returns the current date.  today () 
week_number_of_month 
Returns the week number for the given date in a month.  week_number_of_month(03/23/2017) = 3 
week_number_of_quarter 
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'.  week_number_of_quarter (01/31/2020) = 5 In the following example, May 1st is the start of the fiscal year. week_number_of_quarter (05/31/2020, 'fiscal') = 5 
week_number_of_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'. 
week_number_of_year (01/17/2014) = 3 In the following example, May 1st is the start of the fiscal year. week_number_of_year ( 01/17/2014, 'fiscal') = 38

year 
Returns the 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'. 
year (01/15/2014) = 2014 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. year (12/15/2013, 'fiscal' ) = 2014 year (date ordered) 
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. 
3 != 2 = true revenue != 1000000 
< 
Returns true if the first value is less than the second value.  3 < 2 = false revenue < 1000000 
<= 
Returns true if the first value is less than or equal to the second value.  1 <= 2 = true revenue <= 1000000 
= 
Returns true if the first value is equal to the second value.  2 = 2 = true revenue = 1000000 
> 
Returns true if the first value is greater than the second value.  3 > 2 = true revenue > 1000000 
>= 
Returns true if the first value is greater than or equal to the second value.  3 >= 2 = true revenue >= 1000000 
greatest 
Returns the larger of the values.  greatest (20, 10) = 20 greatest (q1 revenue, q2 revenue) 
least 
Returns the smaller of the values.  least (20, 10) = 10 least (q1 revenue, q2 revenue) 
Number functions
Function  Description  Examples 

* 
Returns the result of multiplying both numbers.  3 * 2 = 6 price * taxrate 
+ 
Returns the result of adding both numbers.  1 + 2 = 3 price + shipping 
 
Returns the result of subtracting the second number from the first.  3  2 = 1 revenue  tax 
/ 
Returns the result of dividing the first number by the second.  6 / 3 = 2 markup / retail price 
^ 
Returns the first number raised to the power of the second.  3 ^ 2 = 9 width ^ 2 
abs 
Returns the absolute value.  abs (10) = 10 abs (profit) 
acos 
Returns the inverse cosine in degrees.  acos (0.5) = 60 acos (cossatelliteangle) 
asin 
Returns the inverse sine (specified in degrees).  asin (0.5) = 30 asin (sinsatelliteangle) 
atan 
Returns the inverse tangent in degrees.  atan (1) = 45 atan (tansatelliteangle) 
atan2 
Returns the inverse tangent in degrees.  atan2 (10, 10) = 45 atan2 (longitude, latitude) 
cbrt 
Returns the cube root of a number.  cbrt (27) = 3 cbrt (volume) 
ceil 
Returns the smallest following integer.  ceil (5.9) = 6 ceil (growth rate) 
cos 
Returns the cosine of an angle (specified in degrees).  cos (63) = 0.45 cos (beam angle) 
cube 
Returns the cube of a number.  cube (3) = 27 cube (length) 
exp 
Returns Euler’s number (~2.718) raised to a power.  exp (2) = 7.38905609893 exp (growth) 
exp2 
Returns 2 raised to a power.  exp2 (3) = 8 exp2 (growth) 
floor 
Returns the largest previous integer.  floor (5.1) = 5 floor (growth rate) 
ln 
Returns the natural logarithm.  ln (7.38905609893) = 2 ln (distance) 
log10 
Returns the logarithm with base 10.  log10 (100) = 2 log10 (volume) 
log2 
Returns the logarithm with base 2 (binary logarithm).  log2 (32) = 5 log2 (volume) 
mod 
Returns the remainder of first number divided by the second number.  mod (8, 3) = 2 mod ( revenue , quantity ) 
pow 
Returns the first number raised to the power of the second number.  pow (5, 2) = 25 pow (width, 2) 
random 
Returns a random number between 0 and 1.  random ( ) = .457718 random ( ) 
round 
Returns the first number rounded to the second number (the default is 1).  round (35.65, 10) = 40 round (battingavg, 100) round (48.67, .1) = 48.7 
safe_divide 
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).  safe_divide (12, 0) = 0 safe_divide (total_cost, units) 
sign 
Returns +1 if the number is greater than zero, 1 if less than zero, 0 if zero.  sign (250) = 1 sign (growth rate) 
sin 
Returns the sine of an angle (specified in degrees).  sin (35) = 0.57 sin (beam angle) 
spherical_distance 
Returns the distance in km between two points on Earth.  spherical_distance (37.465191, 122.153617, 37.421962, 122.142174) = 4,961.96 spherical_distance (start_latitude, start_longitude, start_latitude, start_longitude) 
sq 
Returns the square of a numeric value.  sq (9) = 81 sq (width) 
sqrt 
Returns the square root.  sqrt (9) = 3 sqrt (area) 
tan 
Returns the tangent of an angle (specified in degrees).  tan (35) = 0.7 tan (beam angle) 
Text functions
Function  Description  Examples 

concat 
Returns two or more values as a concatenated text string. Use single quotes around each literal string, not double quotes.  concat ( 'hay' , 'stack' ) = 'haystack' concat (title, ' ', first_name , ' ', last_name) 
contains 
Returns true if the first string contains the second string, otherwise returns false.  contains ('broomstick', 'room') = true contains (product, 'trial version') 
edit_distance 
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.  edit_distance ('attorney', 'atty') = 4 edit_distance (color, 'red') 
edit_distance_with_cap 
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.  edit_distance_with_cap ('pokemon go', 'minecraft pixelmon', 3) = 4 edit_distance_with_cap (event, 'burning man', 3) 
similar_to 
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.  similar_to ('hello world', 'hello swirl') = true similar_to (current team, drafted by) 
similarity 
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.  similarity ('where is the burning man concert', 'burning man') = 46 similarity (tweet1, tweet2) 
spells_like 
Accepts two text strings. Returns true if they are spelled similarly and false if they are not. Works with strings under 1023 characters.  spells_like ('thouhgtspot', 'thoughtspot') = true spells_like (studio, distributor) 
strlen 
Returns the length of the text.  strlen ('smith') = 5 strlen (lastname) 
strpos 
Returns the numeric position (starting from 0) of the first occurrence of the second string in the first string, or 1 if not found.  strpos ('haystack_with_needles', 'needle') = 14 strpos (complaint, 'lawyer') 
substr 
Returns the portion of the given string, beginning at the location specified (starting from 0), and of the given length.  substr ('persnickety', 3, 7) = snicket substr (lastname, 0, 5) 
Embrace passthrough functions
Function  Description  Examples 

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

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

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

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

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

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

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

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

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

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

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

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

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

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