# 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 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:

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

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

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.

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

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

Examples
```day (01/15/2014) = 15
day (date ordered)```
`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. The default is 'calendar'.

Examples
```day_number_of_quarter (01/30/2015) = 30
day_number_of_quarter (01/30/2015, 'fiscal') = 91  // May 1 is start of fiscal year```
`day_number_of_week`

Returns the number (1-7) of the day in a week for a date. Monday is 1, and Sunday is 7.

Examples
```day_number_of_week(01/15/2014) = 3
day_number_of_week (shipped)```
`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 ( 01/30/2015, 'fiscal' ) = 275 // May 1 is start of fiscal year
day_number_of_year (invoiced)```
`day_of_week`

Returns the day of the week for the given date.

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

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

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

Examples
`hour_of_day (received)`
`is_weekend`

Returns `true` if the date is a Saturday or a Sunday.

Examples
```is_weekend (01/31/2015) = true
is_weekend (emailed)```
`month`

Returns the month from the date.

Examples
```month (01/15/2014) = January
month (date ordered)```
`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 ( 09/20/2014, 'fiscal' ) = 5 // May 1 is start of fiscal year
month_number (purchased)```
`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 timestamp.

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 ( 04/14/2014, 'fiscal' ) = 4 // May 1 is start of fiscal year
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.

Examples
```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 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 ( 04/30/2014, 'fiscal') = Feb 2014    // May 1 is the start of the fiscal year
start_of_quarter (sold)```
`start_of_week`

Returns the date for the first day of the week for the given date.

Examples
```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 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'.

Examples
```start_of_year (04/30/2014) returns Jan 2014
start_of_year (04/30/2014, 'fiscal')    // May 1 is start of fiscal year
start_of_year (joined)```
`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.

Examples
`today ()`
`week_number_of_month`

Returns the week number for the date in a month.

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

Default

'calendar'

Examples
```week_number_of_quarter (01/31/2020) = 5
week_number_of_quarter (05/31/2020, 'fiscal') = 5   // May 1 is start of fiscal year```
`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
week_number_of_year ( 01/17/2014, 'fiscal') = 38.  // May 1 is start of fiscal year```
`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'.

Examples
```year (01/15/2014) = 2014
year (12/15/2013, 'fiscal' ) = 2014.  // May 1 is start of fiscal year
year (date ordered)```

## Mixed functions

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

=

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

* (multiply)

Returns the result of multiplying two numbers.

Examples
```3 * 2 = 6
price * taxrate```

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.

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

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

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

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

## Text functions

`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)```
`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)```
`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 (starting from 0) of the first occurrence of the second string in the first string, or -1 if not found.

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

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

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

 ThoughtSpot provides passthrough SQL functions support only for connections in Snowflake.
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

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

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:
``````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

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.

Examples:
``````sql_double_op (
"acosh ({0})",
quantity )

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

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_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})",
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.

Example:
``````sql_string_op (
"soundex ({0})",
"Marks" )``````

[#sql_time_aggregate_op

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

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_op (
"time_from_parts ({0}, {1}, {2})",
12,
30,
20 ) = 12:30:20``````