1. Motive Help Center
  2. Products
  3. Reports and Analytics

Motive Analytics Formulas

Updated

Motive Analytics enables fleets to create custom formulas for calculations while using the provided operators and functions. These formulas help fleets perform more advanced analysis of their data.

 

Creating a new formula

1. Log in to the Fleet Dashboard and click Analytics in the left-hand side menu. 

2. Click Create.

3. Click Create Answer.

image3.png

4. The Custom Answers Builder screen displays. Click Add in the Find columns dropdown.

5. Click Formula.

image1.png

6. The Formula Editor screen displays. Use the predefined functions to create the formula as required. 

7. Once created, click Save. 

Note: Formulas are specific to each answer; if the same formula is required in another answer, fleets have to recreate it there in the new answer.

image2.png

 

Predefined functions

Motive Analytics has multiple predefined functions to help fleets in creating custom formulas. The functions are listed below:

 

Aggregate functions

Fleets can use the following functions to aggregate data.

Basic Aggregates

  • average(column): Returns the average of all values in a column.
    • Example: average(revenue)
  • sum(column): Returns the sum of all values in a column.
    • Example: sum(revenue)
  • count(column): Returns the number of rows in the table containing the column.
    • Example: count(product)
  • unique_count(column): Returns the number of unique values in a column.
    • Example: unique_count(customer)
  • max(column): Returns the maximum value of a column.
    • Example: max(sales)
  • min(column): Returns the minimum value of a column.
    • Example: min(revenue)
  • median(column): Returns the value at the 50th percentile.
    • Example: median(sales)
  • percentile(column, N, order): Returns the value of a column from the row at the Nth percentile.
    • Example: percentile(sales, 99, 'asc')

 

Conditional Aggregates

  • average_if(condition, column): Returns the average of all values that meet a condition.
    • Example: average_if(city = "San Francisco", revenue)
  • sum_if(condition, column): Returns the sum of values that meet a condition.
    • Example: sum_if(region = 'west', revenue)
  • count_if(condition, column): Returns the count of rows that meet a condition.
    • Example: count_if(region = 'west', region)
  • max_if(condition, column): Returns the maximum value of a column that meets a condition.
    • Example: max_if(revenue > 10, customer_region)
  • min_if(condition, column): Returns the minimum value of a column that meets a condition.
    • Example: min_if(revenue < 10, customer_region)
  • unique_count_if(condition, column): Returns the number of unique values of a column that meet a condition.
    • Example: unique_count_if(revenue > 10, order_date)

 

Cumulative Aggregates

  • cumulative_average(measure, attributes...): Returns the cumulative average over specified attributes.
    • Example: cumulative_average(revenue, order_date, state)
  • cumulative_sum(measure, attributes...): Returns the cumulative sum over specified attributes.
    • Example: cumulative_sum(revenue, order_date)
  • cumulative_max(measure, attributes...): Returns the cumulative maximum over specified attributes.
    • Example: cumulative_max(revenue, state)
  • cumulative_min(measure, attributes...): Returns the cumulative minimum over specified attributes.
    • Example: cumulative_min(revenue, campaign)

 

Moving Aggregates (Rolling Windows)

  • moving_average(measure, num1, num2, attributes...): Returns the moving average using a defined window.
    • Example: moving_average(revenue, 2, 1, customer_region)
  • moving_sum(measure, num1, num2, attributes...): Returns the moving sum using a defined window.
    • Example: moving_sum(revenue, 1, 1, order_date)
  • moving_max(measure, num1, num2, attributes...): Returns the moving maximum using a defined window.
    • Example: moving_max(complaints, 1, 2, store_name)
  • moving_min(measure, num1, num2, attributes...): Returns the moving minimum using a defined window.
    • Example: moving_min(defects, 3, 1, product)

 

Ranking & Percentile Functions

  • rank(aggregate_function, order): Returns the rank for the current row.
    • Example: rank(sum(revenue), 'asc')
  • rank_percentile(aggregate_function, order): Returns the percentile rank for the current row.
    • Example: rank_percentile(sum(revenue), 'asc')

 

Statistical Functions

  • stddev(column): Returns the standard deviation of all values in a column.
    • Example: stddev(revenue)
  • stddev_if(condition, column): Returns the standard deviation of values filtered by a condition.
    • Example: stddev_if(revenue > 10, revenue / 10.0)
  • variance(column): Returns the variance of all values in a column.
    • Example: variance(revenue)
  • variance_if(condition, column): Returns the variance of values that meet a condition.
    • Example: variance_if(revenue > 10, revenue / 10.0)

 

Conversion functions

Fleets can use these functions to convert data from one data type to another.

 

to_bool

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

Examples:

  • to_bool(0) = false
  • to_bool(married)

 

to_date

Converts a date represented as an integer or text string into a date format based on a specified format string.

  • Doesn’t accept epoch-formatted dates.
  • Doesn’t accept datetime values, only month, day, and year.

Examples:

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

 

to_double

Returns the input as a double (floating-point) data type.

Examples:

  • to_double('3.14') = 3.14
  • to_double(revenue * 0.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. When converting a date, specify the desired format using strftime (string format time).

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

  • add_seconds(01/30/2015 00:00:00, 5) = 01/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 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 datetime value.
Examples:

  • date(home visit)

 

day

Returns the day of the month (1-31) for a given date. Optionally, specify whether a fiscal or calendar year is used.
Default: Calendar
Examples:

  • day(01/15/2014) = 15
  • day(date ordered)
  • day(01/17/2019, fiscal) = 3 (Assuming the 15th is the start of the fiscal month)

 

day_number_of_quarter

Returns the day number within a quarter for a given date. Optionally, specify a fiscal or calendar year.
Default: Calendar
Examples:

  • day_number_of_quarter(01/30/2015) = 30
  • day_number_of_quarter(01/30/2015, fiscal) = 91 (Assuming May 1 is the fiscal year's start)

 

day_number_of_week

Returns the number (1-7) of the day in a week for a given date. Monday = 1, Sunday = 7. Optionally, specify a fiscal or calendar year.
Default: Calendar
Examples:

  • day_number_of_week(01/15/2014) = 3
  • day_number_of_week(shipped)
  • day_number_of_week(04/28/2022, fiscal) = 2 (Assuming Wednesday starts the fiscal week)

 

day_number_of_year

Returns the day number (1-366) in a year for a given date. Optionally, specify a fiscal or calendar year.
Default: Calendar
Examples:

  • day_number_of_year(01/30/2015) = 30
  • day_number_of_year(invoiced)
  • day_number_of_year(01/30/2015, fiscal) = 275 (Assuming May 1 is the fiscal year's start)

 

day_of_week

Returns the day of the week for a given date. Optionally, specify a fiscal or calendar year.
Default: Calendar
Examples:

  • day_of_week(01/30/2015) = Friday
  • day_of_week(serviced)
  • day_of_week(04/28/2022, fiscal) = jeudi (Days in French for the fiscal calendar)

 

diff_days

Returns the difference between two dates in 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

Returns the difference between two timestamps in hours.
Examples:

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

 

diff_minutes

Returns the difference between two timestamps in minutes.
Examples:

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

 

diff_months

Returns the difference between two dates in months. Optionally, specify a custom calendar.
Examples:

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

 

diff_time

Returns the difference between two timestamps in seconds.
Examples:

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

 

is_weekend

Returns true if the given date falls on a weekend (Saturday or Sunday). Optionally, specify a fiscal or calendar year.
Default: Calendar
Examples:

  • is_weekend(01/31/2015) = true
  • is_weekend(04/28/2022, fiscal) = true (Assuming Wednesday and Thursday are fiscal weekends)

 

month

Returns the month name for a given date. Optionally, specify a fiscal or calendar year.
Default: Calendar
Examples:

  • month(01/15/2014) = January
  • month(08/20/2014, fiscal) = 

 

month_number

Returns the number (1-12) of the month from a given date.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • month_number(09/20/2014) → 9
  • month_number(09/20/2014, fiscal) → 5 (if May 1 is the first day of the fiscal year)

 

month_number_of_quarter

Returns the month number (1-3) within a quarter for the given date.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • month_number_of_quarter(02/20/2018) → 2
  • month_number_of_quarter(02/20/2018, fiscal) → 1 (if May 1 is the first day of the fiscal year)

 

now

Returns the current date and time in the locale’s standard format.

Example:

  • now() → 04/27/2022 12:34:00 (for U.S. locale)

 

quarter_number

Returns the quarter number (1-4) for the given date.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • quarter_number(04/14/2014) → 2
  • quarter_number(04/14/2014, fiscal) → 4 (if May 1 is the first day of the fiscal year)

 

start_of_month

Description: Returns the first day of the month in MMM yyyy format (configurable).

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • start_of_month(01/31/2015) → Jan FY 2015
  • start_of_month(01/14/2022, fiscal) → Dec 2021 (if the 15th is the start of the fiscal month)

 

start_of_quarter

Returns the date for the first quarter day for the given date.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • start_of_quarter(04/30/2014) → Apr 2014
  • start_of_quarter(04/30/2014, fiscal) → Feb 2014 (if May 1 is the first day of the fiscal year)

 

start_of_week

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

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • start_of_week(01/31/2020) → 01/27/2020
  • start_of_week(04/28/2022, fiscal) → 04/27/2022 (if Wednesday is the start of the fiscal week)

 

start_of_year

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

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • start_of_year(04/30/2014) → Jan 2014
  • start_of_year(04/30/2014, fiscal) → May 2014 (if May 1 is the first day of the fiscal year)

 

time

Returns the time portion of a given date.

Examples:

  • time(1/31/2002 10:32) → 10:32

 

today

Returns the current date in your locale’s standard date format (e.g., MM/dd/yyyy for U.S. locale).

Example:

  • today() → 04/27/2022

 

week_number_of_month

Returns the week number for the given date in a month.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • week_number_of_month(03/23/2017) → 3
  • week_number_of_month(05/31/2020, fiscal) → 3 (if the 15th is the start of the fiscal month)

 

week_number_of_quarter

Returns the week number for the given date in a quarter.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • week_number_of_quarter(01/31/2020) → 5
  • week_number_of_quarter(05/31/2020, fiscal) → 5 (if May 1 is the first day of the fiscal year)

 

week_number_of_year

Returns the week number for the given date in a year.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • week_number_of_year(01/17/2014) → 3
  • week_number_of_year(01/17/2014, fiscal) → 38 (if May 1 is the first day of the fiscal year)

 

year

Returns the year in integer format for the given date.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • year(01/15/2014) → 2014
  • year(12/15/2013, fiscal) → 2014 (if May 1 is the first day of the fiscal year)

 

year_name

Returns the year in string format for the given date.

Optional second parameter: Specifies fiscal or calendar year (default: calendar).

Examples:

  • year_name(01/15/2014) → "2014"
  • year_name(12/15/2013, fiscal) → "FY_2014" (if May 1 is the first day of the fiscal year)

 

Mixed Functions

=

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

Examples:

  • 2 = 2 → true
  • revenue = 1000000 → true

 

>

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

Examples:

  • 3 > 2 → true
  • revenue > 1000000 → false

 

>=

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

Examples:

  • 3 >= 2 → true
  • revenue >= 1000000 → true

 

greatest

Returns the larger of the values.

Examples:

  • greatest(20, 10) → 20
  • greatest(q1_revenue, q2_revenue) → depends on the values of q1_revenue and q2_revenue

 

least

Returns the smaller of the values.

Examples:

  • least(20, 10) → 10
  • least(q1_revenue, q2_revenue) → depends on the values of q1_revenue and q2_revenue

 

<

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

Examples:

  • 3 < 2 → false
  • revenue < 1000000 → true

 

<=

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

Examples:

  • 1 <= 2 → true
  • revenue <= 1000000 → true

 

!=

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

Examples:

  • 3 != 2 → true
  • revenue != 1000000 → false

 

Number Functions

Multiplication (*)

Returns the result of multiplying two numbers.
Examples:

  • 3 * 2 = 6
  • price * taxrate

 

Addition (+)

Returns the result of adding two numbers.
Examples:

  • 1 + 2 = 3
  • price + shipping

 

Subtraction (-)

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

  • 3 - 2 = 1
  • revenue - tax

 

Division (/)

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

  • 6 / 3 = 2
  • markup / retail_price

 

Exponentiation (^ or pow function)

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

  • 3 ^ 2 = 9
  • pow(5, 2) = 25
  • width ^ 2

 

Absolute Value & Rounding Functions

Absolute Value (abs)

Returns the absolute value of a number.
Examples:

  • abs(-10) = 10
  • abs(profit)

 

Ceiling (ceil)

Rounds a number up to the nearest integer.
Examples:

  • ceil(5.9) = 6
  • ceil(growth_rate)

 

Floor (floor)

Rounds a number down to the nearest integer.
Examples:

  • floor(5.1) = 5
  • floor(growth_rate)

 

Rounding (round)

Rounds the first number to the precision specified by the second number (default is 1).
Examples:

  • round(35.65, 10) = 40
  • round(48.67, 0.1) = 48.7

 

Trigonometric Functions (Degrees)

Cosine (cos)

Returns the cosine of an angle in degrees.
Examples:

  • cos(63) = 0.45
  • cos(beam_angle)

 

Sine (sin)

Returns the sine of an angle in degrees.
Examples:

  • sin(35) = 0.57
  • sin(beam_angle)

 

Tangent (tan)

Returns the tangent of an angle in degrees.
Examples:

  • tan(35) = 0.7
  • tan(beam_angle)

 

Inverse Cosine (acos)

Returns the inverse cosine in degrees.
Examples:

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

 

Inverse Sine (asin)

Returns the inverse sine in degrees.
Examples:

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

 

Inverse Tangent (atan)

Returns the inverse tangent in degrees.
Examples:

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

 

Inverse Tangent with Two Parameters (atan2)

Returns the inverse tangent in degrees using two values.
Examples:

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

 

Exponential & Logarithmic Functions

Euler’s Number (exp)

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

  • exp(2) = 7.389
  • exp(growth)

 

Base-2 Exponentiation (exp2)

Returns 2 raised to a given power.
Examples:

  • exp2(3) = 8
  • exp2(growth)

 

Natural Logarithm (ln)

Returns the natural logarithm of a number.
Examples:

  • ln(7.389) = 2
  • ln(distance)

 

Base-10 Logarithm (log10)

Returns the logarithm of a number in base 10.
Examples:

  • log10(100) = 2
  • log10(volume)

 

Base-2 Logarithm (log2)

Returns the logarithm of a number in base 2.
Examples:

  • log2(32) = 5
  • log2(volume)

 

Modulus & Special Functions

Modulo (mod)

Returns the remainder when dividing the first number by the second.
Examples:

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

 

Safe Division (safe_divide)

Divides the first number by the second, returning 0 if division by zero occurs.
Examples:

  • safe_divide(12, 0) = 0
  • safe_divide(total_cost, units)

 

Sign (sign)

Returns +1 if the number is positive, -1 if negative, and 0 if zero.
Examples:

  • sign(-250) = -1
  • sign(growth_rate)

 

Random Number (random)

Returns a random number between 0 and 1.
Examples:

  • random() = 0.457718
  • random()

 

Root & Power Functions

Square (sq)

Returns the square of a number (raises it to the power of 2).
Examples:

  • sq(9) = 81
  • sq(width)

 

Square Root (sqrt)

Returns the square root of a number.
Examples:

  • sqrt(9) = 3
  • sqrt(area)

 

Cube (cube)

Returns the cube of a number (raises it to the power of 3).
Examples:

  • cube(3) = 27
  • cube(length)

 

Cube Root (cbrt)

Returns the cube root of a number.
Examples:

  • cbrt(27) = 3
  • cbrt(volume)

 

ceil (Ceiling)

Rounds a number up to the nearest integer.
Examples:

  • ceil(5.9) = 6
  • ceil(growth_rate)

 

cos (Cosine)

Returns the cosine of an angle specified in degrees.
Examples:

  • cos(63) = 0.45
  • cos(beam_angle)

 

cube

Returns the cube of a number (raises it to the power of 3).
Examples:

  • cube(3) = 27
  • cube(length)

 

exp (Exponential)

Returns Euler’s number (≈2.718) raised to the given power.
Examples:

  • exp(2) = 7.38905609893
  • exp(growth)

 

exp2

Returns 2 raised to the given power.
Examples:

  • exp2(3) = 8
  • exp2(growth)

 

floor

Rounds a number down to the nearest integer.
Examples:

  • floor(5.1) = 5
  • floor(growth_rate)

 

ln (Natural Logarithm)

Returns the natural logarithm (base e) of a number.
Examples:

  • ln(7.38905609893) = 2
  • ln(distance)

 

log10 (Base 10 Logarithm)

Returns the logarithm of a number with base 10.
Examples:

  • log10(100) = 2
  • log10(volume)

 

log2 (Base 2 Logarithm)

Returns the logarithm of a number with base 2.
Examples:

  • log2(32) = 5
  • log2(volume)

 

mod (Modulo)

Returns the remainder of division of the first number by the second number.
Examples:

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

 

pow (Power)

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() = 0.457718
  • random()

 

round

Rounds the first number to the nearest multiple of the second number (default is 1).
Examples:

  • round(35.65, 10) = 40
  • round(batting_avg, 100)
  • round(48.67, .1) = 48.7

 

safe_divide

Returns the result of division. If the divisor is 0, it returns 0 instead of NaN.
Examples:

  • safe_divide(12, 0) = 0
  • safe_divide(total_cost, units)

 

sign

Returns +1 if the number is positive, -1 if negative, and 0 if the number is zero.
Examples:

  • sign(-250) = -1
  • sign(growth_rate)

 

sin (Sine)

Returns the sine of an angle specified in degrees.
Examples:

  • sin(35) = 0.57
  • sin(beam_angle)

 

spherical_distance

Returns the distance (in km) between two points on Earth, given their latitude and longitude.
Parameters Order: lat1, long1, lat2, long2.
Examples:

plaintext

CopyEdit

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

spherical_distance(start_latitude, start_longitude, end_latitude, end_longitude)

 

sq (Square)

Returns the square of a number (raises it to the power of 2).
Examples:

  • sq(9) = 81
  • sq(width)

 

sqrt (Square Root)

Returns the square root of a number.
Examples:

  • sqrt(9) = 3
  • sqrt(area)

 

tan (Tangent)

Returns the tangent of an angle 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'

 

if…then…else

Conditional operator allowing 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

Checks if a column value matches any value in a specified list.

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

Checks if a column value doesn’t match any value in a specified list.

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'

 

Text Functions

concat

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

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 as an upper limit cap for the edit distance.

  • If the edit distance is less than or equal to the cap, returns the edit distance.
  • If it exceeds the cap, returns the cap +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 a substring of the specified length from the left side of a given string.

Examples:

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

 

right

Returns a substring of the specified length from the right side of a given string.

Examples:

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

 

similar_to

Accepts a document text string and a search text string.

  • Returns true if the relevance score (0-100) is ≥ 20.
  • Relevance is based on edit distance, number of words in the query, and the length of words 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.
  • 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, otherwise returns false.

Examples:

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

 

spells_like

Accepts two text strings.

  • Returns true if they are spelled similarly, otherwise returns false.
  • Works with strings under 1023 characters.

Examples:

  • spells_like('motive', 'mitove') = true
  • spells_like(studio, distributor)

 

strlen

Returns the length of the given text.

Examples:

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

 

strpos

Returns the numeric position of the first occurrence of the second string in the first string.

Examples:

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

 

substr

Returns a substring from a given string, starting at a specified position (index starts at 0) and for a given length.

Examples:

  • substr('persnickety', 3, 7) = 'snicket'
  • substr(lastname, 0, 5)
Was this article helpful?

Share this with others