Row-level security rules reference

ThoughtSpot allows you to create row-level security rules using expressions. If an expression evaluates to "true" for a particular row and group combination, that group will be able to see that row. This reference lists the various operators and functions you can use to create rules.

For information on how to use the row-level security functions and operators, see About rule-based row-level security. There is a special variable called ts_groups, which you can use when creating row-level security rules. It fetches a list of the groups that the currently logged-in user belongs to. For each row, if the expression in the rule evaluates to 'true' for any one of these groups, that row will be shown to the user.

You can also see this list of operators and examples from within the Rule Builder by selecting Rule Assistant.

Conversion functions

These functions can be used to convert data from one data type to another. Conversion to or from date data types is not supported.

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. Does not accept datetime values. Only accepts month, day, and year.

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

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 (1-31) 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.

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 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 (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. The default is calendar.

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

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.

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.

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.

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.

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.

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.

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.

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.

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. The default is calendar.

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. The default is calendar.

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. The default is calendar.

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

In the following example, May 1st is the start 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. 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 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).

now ()

quarter_number

Returns the number (1-4) 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. 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_month ( 01/31/2015 ) = Jan 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 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. 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_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 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 in your locale’s standard date format. For example, if your locale is English (United States), it returns MM/dd/yyyy (04/27/2022).

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. The default is calendar.

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

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 year-end date.

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

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. The default is calendar.

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

In the following example, May 1st is the start of the fiscal year. Per standard convention, the fiscal year is defined by the year-end date.

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

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 (cos-satellite-angle)

asin

Returns the inverse sine (specified in degrees).

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

atan

Returns the inverse tangent in degrees.

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

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). For example, to round to two digits, you would enter round (measure, .01).

round (35.65, 10) = 40
round (battingavg, 100)
round (48.67, .1) = 48.7
round (50.8634, .001) = 50.863

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, end_latitude, end_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)

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'

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

if…​then…​else

Conditional operator. Accepts multiple clauses.

if ( item type in {'shirts', 'jackets', 'sweatshirts', 'sweaters'}) then 'tops' else if ( item type in {'shorts', 'pants'}) then 'bottoms' else 'all other apparel'
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')

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.

state in { 'texas' , 'california' }

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

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.

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

or

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

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

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)

left

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

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

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

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.

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.

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.

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.

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 of the first occurrence of the second string in the first string. The position starts at 1, and 0 indicates not found.

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.

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

Variables

These variables can be used in your expressions.

Function Description Examples

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.

ts_groups = 'east'

ts_username

Returns the user with the matching name.

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.