Formula function reference

Learn the operators and functions you can use to create formulas in ThoughtSpot.

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 consists of n combinations of operators and functions.

This reference lists the various operators and functions you can use to create formulas.

Aggregate functions (group aggregate)

Use the following functions to aggregate data.

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 that meets the specified condition.

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 specify columns and filters to include or ignore in your query. Commonly used in comparison analysis.

This formula takes the following form:

group_aggregate (<aggregation(measure)>, <groupings>, <filters>)

Define lists using curly brackets, { }. Optional list functions query_groups or query_filters specify the lists or filters used in the original search. Use + (plus) and - (minus) to add or exclude specific columns for query groups.

Beta

Specify columns in the third argument of a group_aggregate function to include only the filters defined in the search bar that are on that column. Other filters not on columns defined in the third argument of the function will be ignored. This feature is in Beta and off by default. To enable it, contact ThoughtSpot Support. For more about Beta features, see ThoughtSpot Software release life cycle.

See Flexible aggregation functions.

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 )

median

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

median (sales)

Median is not supported when the resulting query:

is a complex query (chasm/fan trap) with non-shared attribute. This is referred to as an attribution query.
has group-by-group_aggregate
has a reusable set
has a query with an IN-filter query
is displayed as a pivot table
table aggregate summary over median column

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)

percentile: Returns the value of the measure from the row that has a rank_percentile less than or equal to N. If there is no rank_percentile below N, the value of the measure of the first row above N will be returned.

percentile (sales , 99 , 'asc' )
percentile (sales , 99, 'desc' )

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 the specified condition.

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.

to_bool
to_date
to_double
to_integer
to_string

to_bool

Returns the input as a boolean data type (true` or false).

Examples

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.

Does not accept datetime values. Only accepts month, day, and year.

Examples

to_date (date_sold, '%Y-%m-%d')

to_double

Returns the input as a double data type.

Examples

to_double ('3.14') = 3.14
to_double (revenue * .01)

to_integer

Returns the input as an integer.

Examples

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 in the second argument (for example, to_string ( <date column> , "%Y-%m-%d" )). Use strftime for the date format.

Examples

to_string (45 + 1) = '46'
to_string (revenue - cost)
to_string (date,'%m/%d/%y')

Date functions

add_days

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

Examples

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, or time.

Examples

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.

Examples

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.

Examples

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.

Examples

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.

Examples

add_years ( 01/30/2015, 5 ) = 01/30/2020
add_years ( invoiced_date , 5 )

date

Returns the date portion of a date.

Examples

date (home visit)

day

Returns the number (1-31) of the day of the month for a date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.

Default

calendar

Examples

day (01/15/2014) = 15
day (date ordered)

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

day (01/17/2019, fiscal) = 3

day_number_of_quarter

Returns the number of the day in a quarter for a date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.

Default

calendar

Examples

day_number_of_quarter (01/30/2015) = 30

In the following example, May 1 is the first day of the fiscal year.

day_number_of_quarter (01/30/2015, fiscal) = 91

day_number_of_week

Returns the number (1-7) 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.

Default

calendar

Examples

day_number_of_week(01/15/2014) = 3
day_number_of_week (shipped)

In the following example, Wednesday is the start of the fiscal week.

day_number_of_week(04/28/2022, fiscal) = 2

day_number_of_year

Returns the number (1-366) of the day in a year from a date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.

Default

calendar

Examples

day_number_of_year (01/30/2015) = 30
day_number_of_year (invoiced)

In the following example, May 1 is the first day of the fiscal year.

day_number_of_year ( 01/30/2015, fiscal ) = 275

day_of_week

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.

Default

calendar

Examples

day_of week (01/30/2015) = Friday
day_of_week (serviced)

In the following example, the days of the week are in French in the fiscal calendar.

day_of_week(04/28/2022, fiscal) = jeudi

diff_days

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

Examples

diff_days (01/15/2014, 01/17/2014) = -2
diff_days (01/15/2014 01:00:00, 01/14/2014 23:00:00) = 1
diff_days (purchased, shipped)

diff_hours

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

Examples

diff_hours (01/15/2014 01:59:59, 01/15/2014 02:00:00) = -1
diff_hours (01/15/2014 01:00:00, 01/15/2014 01:59:59) = 0
diff_hours (clicked, submitted)

diff_minutes

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

Examples

diff_minutes (01/15/2014 01:59:59, 01/15/2014 02:00:00) = -1
diff_minutes (01/15/2014 01:00:00, 01/15/2014 01:00:59) = 0
diff_minutes (clicked, submitted)

diff_months

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.

Examples

diff_months (12/25/2013, 01/01/2014) = -1
diff_months (01/01/2014, 01/25/2014) = 0
diff_months (purchased, shipped)
diff_months (purchased, shipped, fiscal)

diff_quarters

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.

Examples

diff_quarters (12/31/2013, 01/01/2014) = -1
diff_quarters (01/01/2014, 03/31/2014) = 0
diff_quarters (purchased, shipped)
diff_quarters (purchased, shipped, fiscal)

diff_time

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

Examples

diff_time (01/30/2014, 01/31/2014) = -86,400
diff_time (clicked, submitted)

diff_weeks

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.

Examples

diff_weeks (01/05/2014, 01/06/2014) = -1
diff_weeks (01/06/2014, 01/12/2014) = 0
diff_weeks (purchased, shipped)
diff_weeks (purchased, shipped, fiscal)

diff_years

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.

Examples

diff_years (12/25/2013, 01/01/2014) = -1
diff_years (01/01/2014, 12/25/2014) = 0
diff_years (purchased, shipped)
diff_years (purchased, shipped, fiscal)

hour_of_day

Returns the hour of the day for the given date.

Examples

hour_of_day (received)

is_weekend

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.

Default

calendar

Examples

is_weekend (01/31/2015) = true
is_weekend (emailed)

In the following example, Wednesday and Thursday are the days of the weekend in the fiscal week.

is_weekend (04/28/2022, fiscal) = true

month

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.

Default

calendar

Examples

month (01/15/2014) = January
month (date ordered)

In the following example, the months of the year are in Spanish in the fiscal calendar.

month ( 08/20/2014, fiscal ) = agosto

month_number

Returns the number (1-12) 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.

Default

calendar

Examples

month_number (09/20/2014) = 9
month_number (purchased)

In the following example, May 1 is the first day of the fiscal year.

month_number ( 09/20/2014, fiscal ) = 5

month_number_of_quarter

Returns the month (1-3) 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.

Default

calendar

Examples

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 date and time in your locale’s standard date and time format. For example, if your locale is English (United States), it returns MM/dd/yyyy hh:mm:ss (04/27/2022 12:34:00).

Examples

now ()

quarter_number

Returns the number (1-4) of the quarter associated with the date. You can add an optional second parameter to specify fiscal or calendar dates.

Default

calendar

Examples

quarter_number ( 04/14/2014) = 2
quarter_number ( shipped )

In the following example, May 1 is the first day of the fiscal year.

quarter_number ( 04/14/2014, fiscal ) = 4

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. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.

Default

calendar

Examples

start_of_month ( 01/31/2015 ) = Jan FY 2015
start_of_month (shipped)

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

start_of_month ( 01/14/2022, fiscal) = Dec 2021

start_of_quarter

Returns the date for the first day of the quarter for the date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.

Default

calendar

Examples

start_of_quarter ( 04/30/2014) = Apr 2014
start_of_quarter (sold)

In the following example, May 1 is the first day of the fiscal year.

start_of_quarter ( 04/30/2014, fiscal) = Feb 2014

start_of_week

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.

Default

calendar

Examples

start_of_week ( 01/31/2020 ) = 01/27/2020
start_of_week (emailed)

In the following example, Wednesday is the start of the fiscal week.

start_of_week ( 04/28/2022, fiscal) = 04/27/2022

start_of_year

Returns the date for the first day of the year for the date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.

Default

calendar

Examples

start_of_year (04/30/2014) returns Jan 2014
start_of_year (joined)

In the following example, May 1 is the first day of the fiscal year.

start_of_year (04/30/2014, fiscal)

time

Returns the time portion of a date.

Examples

time (1/31/2002 10:32) = 10:32
time (call began)

today

Returns the current date in your locale’s standard date format. For example, if your locale is English (United States), it returns MM/dd/yyyy (04/27/2022).

Examples

today ()

week_number_of_month

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.

Default

calendar

Examples

week_number_of_month(03/23/2017) = 3

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

week_number_of_month (05/31/2020, fiscal) = 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.

Default

calendar

Examples

week_number_of_quarter (01/31/2020) = 5

In the following example, May 1 is the first day of the fiscal year.

week_number_of_quarter (05/31/2020, fiscal) = 5

week_number_of_year

Returns the week number for the 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.

Default

calendar

Examples

week_number_of_year (01/17/2014) = 3

In the following example, May 1 is the first day of the fiscal year.

week_number_of_year ( 01/17/2014, fiscal) = 38

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.

Default

calendar

Examples

year (01/15/2014) = 2014
year (date ordered)

In the following example, May 1 is the first day of the fiscal year.

year (12/15/2013, fiscal ) = 2014

year_name

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.

Default

calendar

Examples

year (01/15/2014) = "2014"
year (date ordered)

In the following example, May 1 is the first day of the fiscal year.

year (12/15/2013, fiscal ) = "FY_2014"

Mixed functions

These functions can be used with text and numeric data types.

=
>=
>, greater than
greatest
least
⇐, less or equal
<, less than
!=

=

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

Examples

2 = 2 = true
revenue = 1000000

>

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

Examples

3 > 2 = true
revenue > 1000000

>=

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

Examples

3 >= 2 = true
revenue >= 1000000

greatest

Returns the larger of the values.

Examples

greatest (20, 10) = 20
greatest (q1 revenue, q2 revenue)

least

Returns the smaller of the values.

Examples

least (20, 10) = 10
least (q1 revenue, q2 revenue)

<

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

Examples

3 < 2 = false
revenue < 1000000

<=

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

Examples

1 <= 2 = true
revenue <= 1000000

!=

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

Examples

3 != 2 = true
revenue != 1000000

Number functions

*
+
-
/
power
abs
acos
asin
atan
atan2
cbrt
ceil
cos
cube
exp
exp2
floor
ln
log10
log2
mod
pow
random
round
safe_divide
sign
sin
spherical_distance
sq
sqrt
tan

* (multiply)

Returns the result of multiplying two numbers.

Examples

3 * 2 = 6
price * taxrate

+ (add)

Returns the result of adding two numbers.

Examples

1 + 2 = 3
price + shipping

− (subtract)

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

Examples

3 - 2 = 1
revenue - tax

/ (divide)

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

Examples

6 / 3 = 2
markup / retail price

^

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

Examples

3 {caret} 2 = 9
width {caret} 2

abs

Returns the absolute value of a number.

Examples

abs (-10) = 10
abs (profit)

acos

Returns the inverse cosine, in degrees.

Examples

acos (0.5) = 60
acos (cos-satellite-angle)

asin

Returns the inverse sine, in degrees.

Examples

asin (0.5) = 30
asin (sin-satellite-angle)

atan

Returns the inverse tangent, in degrees.

Examples

atan (1) = 45
atan (tan-satellite-angle)

atan2

Returns the inverse tangent, in degrees.

Examples

atan2 (10, 10) = 45
atan2 (longitude, latitude)

cbrt

Returns the cube root of a number.

Examples

cbrt (27) = 3
cbrt (volume)

ceil

Returns the rounded up integer value of a fraction.

Examples

ceil (5.9) = 6
ceil (growth rate)

cos

Returns the cosine of an angle that is specified in degrees.

Examples

cos (63) = 0.45
cos (beam angle)

cube

Returns the cube of a number, or the number to the 3rd power.

Examples

cube (3) = 27
cube (length)

exp

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

Examples

exp (2) = 7.38905609893
exp (growth)

exp2

Returns 2 raised to a power specified by the number.

Examples

exp2 (3) = 8
exp2 (growth)

floor

Returns the rounded down integer value of a fraction.

Examples

floor (5.1) = 5
floor (growth rate)

ln

Returns the natural logarithm of a number.

Examples

ln (7.38905609893) = 2
ln (distance)

log10

Returns the base 10 logarithm of a number.

Examples

log10 (100) = 2
log10 (volume)

log2

Returns the base 2 logarithm, or the binary logarithm, of a number.

Examples

log2 (32) = 5
log2 (volume)

mod

Returns the remainder of a division of the first number by the second number.

Examples

mod (8, 3) = 2
mod ( revenue , quantity )

pow

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

Examples

pow (5, 2) = 25
pow (width, 2)

random

Returns a random number between 0 and 1.

Examples

random ( ) = .457718
random ( )

round

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

Examples

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

Examples

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.

Examples

sign (-250) = -1
sign (growth rate)

sin

Returns the sine of an angle that is specified in degrees.

Examples

sin (35) = 0.57
sin (beam angle)

spherical_distance

Returns the distance, in km, between two points on Earth, as defined by their latitude and longitude.

The order of parameters is: lat1, long1, lat2, long2.

Examples

spherical_distance (
  37.465191, -122.153617,
  37.421962, -122.142174) = 4,961.96

spherical_distance (
  start_latitude, start_longitude,
  end_latitude, end_longitude)

sq

Returns the square of a numeric value, or the number to the power of 2.

Examples

sq (9) = 81
sq (width)

sqrt

Returns the square root of a number, or the number to the power of 1/2.

Examples

sqrt (9) = 3
sqrt (area)

tan

Returns the tangent of an angle that is specified in degrees.

Examples

tan (35) = 0.7
tan (beam angle)

Operators

and

Returns true when both conditions are true, otherwise returns false.

Examples

(1 = 1) and (3 > 2) = true
lastname = 'smith' and state ='texas'

Not available for row-level security (RLS) formulas.

if…then…else

Conditional operator. Allows for multiple clauses.

Examples

if (cost > 500) then 'flag' else 'approve'
if ( item type in {'shirts', 'jackets', 'sweatshirts', 'sweaters'}) then 'tops' else if ( item type in {'shorts', 'pants'}) then 'bottoms' else 'all other apparel'

ifnull

Returns the first value if it is not null, otherwise returns the second value.

Example

ifnull (cost, 'unknown')

in

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

Example

state in { 'texas' , 'california' }

isnull

Returns true if the value is null.

Example

isnull (phone)

not

Returns true if the condition is false, otherwise returns false.

Examples

not (3 > 2) = false
not (state = 'texas')

not in

Takes a column name and a list of values. It checks each column value against the list of values in the formula, and returns true if the column value does not match any of the values in the formula.

Example

state not in { 'texas' , 'california' }

or

Returns true when either condition is true, otherwise returns false.

Examples

(1 = 5) or (3 > 2) = true
state = 'california' or state ='oregon'

concat

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

Examples

concat ( 'hay' , 'stack' ) = 'haystack'
concat (title, ' ', first_name , ' ', last_name)

contains

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

Examples

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.

Examples

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.

Examples

edit_distance_with_cap ('pokemon go', 'minecraft pixelmon', 3) = 4
edit_distance_with_cap (event, 'burning man', 3)

left

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

Examples

left ( 'persnickety' , 4 ) = 'pers'
left ( lastname , 5 )

right

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

Examples

right ( 'persnickety' , 4 ) = 'kety'
right ( lastname , 5 )

similar_to

Accepts a document text string and a search text string. Returns true if relevance score (0-100) 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.

Examples

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 (0-100) 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.

Examples

similarity ('where is the burning man concert', 'burning man') = 46
similarity (tweet1, tweet2)

sounds_like

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

Examples

sounds_like ( 'read' , 'red' ) = true
sounds_like ( owner , promoter )

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.

Examples

spells_like ('thouhgtspot', 'thoughtspot') = true
spells_like (studio, distributor)

strlen

Returns the length of the text.

Examples

strlen ('smith') = 5
strlen (lastname)

strpos

Returns the numeric position of the first occurrence of the second string in the first string. If using an external cloud data warehouse connection, the position starts at 1, and 0 indicates not found. If using ThoughtSpot’s internal in-memory database (Falcon), the position starts at 0, and -1 indicates not found.

Examples

In-memory database: strpos ('haystack_with_needles', 'needle') = 14
External database: strpos ('haystack_with_needles', 'needle') = 15
strpos (complaint, 'lawyer')

substr

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

Examples

substr ('persnickety', 3, 7) = snicket
substr (lastname, 0, 5)

Variables

These variables can be used in your expressions.

ts_groups

Returns a list of all the groups the current logged-in user belongs to. For any row, if the expression evaluates to true for any of the groups, the user can see that row.

Example

ts_groups = 'east'

ts_username

Returns the user with the matching name.

Example

ts_username != 'mark'

You cannot use these variables (ts_groups and ts_username) within an expression. For example, ts_groups = substr(rls_group_name, 0, 3) is valid, but substr(ts_groups,0,3) = rls_group_name is NOT valid.

Connection passthrough functions

The following passthrough SQL functions are supported in connections for all cloud data warehouses:

sql_bool_aggregate_op
sql_bool_op
sql_date_aggregate_op
sql_date_op
sql_date_time_aggregate_op
sql_date_time_op
sql_double_aggregate_op
sql_double_op
sql_int_aggregate_op
sql_int_op
sql_string_aggregate_op
sql_string_op
sql_time_aggregate_op
sql_time_op

sql_bool_aggregate_op

Returns the boolean data type. The first argument takes the signature of the external function and runs it against the datasource. Subsequent arguments pass the values to the external function.

Example

sql_bool_aggregate_op (
   "booland_agg ({0})",
   is_delivered )

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.

Examples:

sql_bool_op (
   "is_decimal ({0})",
   itemCount )

sql_bool_op (
   "boolor ({0}, {1})",
   2,
   0 ) = True

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.

Example:

sql_date_aggregate_op (
   "max ({0})" ,
   orderdate )

sql_date_op

Example:

sql_date_op (
   "previous_day ({0})",
   ship_date )

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.

Example:

ql_date_time_aggregate_op (
   "max ({0})",
   delivery_time )

sql_date_time_op

Example:

sql_date_time_op (
   "timestamp_sub ({0}, {1})",
   sale_time,
   'INTERVAL 30 MINUTE')

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.

Example:

sql_double_aggregate_op (
   "approx_percentile ({0}, {1})",
   unrealised_gain,
   0.99 )

sql_double_op

Examples:

sql_double_op (
   "acosh ({0})",
   quantity )

sql_double_op (
   "radians ({0})",
   180 ) = 3.141592654

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.

Examples:

sql_int_aggregate_op (
   "approx_count_distinct ({0})",
   sale_volume )

sql_int_aggregate_op (
   "bitand_agg({0}) OVER ( [ partition by {1} ] )",
   user_permissions,
   user_type )

sql_int_op

Examples:

sql_int_op (
   "ceil ({0})",
   itemCount )
sql_int_op (
   "charindex ({0}, {1})",
   "qwerty",
   "rty" ) = 4

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.

Example:

sql_string_aggregate_op (
   "min ({0})",
   username )

sql_string_op

Example:

sql_string_op (
   "soundex ({0})",
   "Marks" )

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.

Example:

sql_time_aggregate_op (
   "max (time ({0}))",
   delivery_time )

sql_time_op

Example:

sql_time_op (
   "time_from_parts ({0}, {1}, {2})",
   12,
   30,
   20 ) = 12:30:20

Was this page helpful?Give us feedback!

ThoughtSpot is the Modern Analytics Cloud company. Our Live Analytics services deliver personalized, actionable insights at the point of impact for every user, at every level.

Product

By Role

By Department

Solutions

About Us

Connect

(800) 508-7008