What are Motive Analytics formulas?

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

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

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

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

Examples:

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

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

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

Examples:

• to_double('3.14') = 3.14
• to_double(revenue * 0.01)

Returns the input as an integer.

Examples:

• to_integer('45') + 1 = 46
• to_integer(price + tax - cost)

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

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)

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)

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)

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)

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)

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)

Returns the date portion of a datetime value.
Examples:

• date(home visit)

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)

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)

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)

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)

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)

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)

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)

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)

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)

Returns the difference between two timestamps in seconds.
Examples:

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

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)

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

Returns the month's number (1-12) 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)

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)

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

Example:

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

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)

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)

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)

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)

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)

Returns the time portion of a given date.

Examples:

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

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

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)

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)

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)

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)

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)

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

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

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

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

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

• 3 - 2 = 1
• revenue - tax

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

• 6 / 3 = 2
• markup / retail_price

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

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

Returns the absolute value of a number.
Examples:

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

Rounds a number up to the nearest integer.
Examples:

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

Rounds a number down to the nearest integer.
Examples:

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

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

Returns the cosine of an angle in degrees.
Examples:

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

Returns the sine of an angle in degrees.
Examples:

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

Returns the tangent of an angle in degrees.
Examples:

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

Returns the inverse cosine in degrees.
Examples:

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

Returns the inverse sine in degrees.
Examples:

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

Returns the inverse tangent in degrees.
Examples:

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

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

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

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

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

Returns 2 raised to a given power.
Examples:

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

Returns the natural logarithm of a number.
Examples:

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

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

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

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

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

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

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

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)

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

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

Returns a random number between 0 and 1.
Examples:

• random() = 0.457718
• random()

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

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

Returns the square root of a number.
Examples:

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

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

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

Returns the cube root of a number.
Examples:

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

Rounds a number up to the nearest integer.
Examples:

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

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

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

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

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

Returns 2 raised to the given power.
Examples:

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

Rounds a number down to the nearest integer.
Examples:

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

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

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

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

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

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

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

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

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

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

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

Returns a random number between 0 and 1.
Examples:

• random() = 0.457718
• random()

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

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)

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

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

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

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

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)

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

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

Returns the square root of a number.
Examples:

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

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

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

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

Examples:

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

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'

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

Example:

• ifnull(cost, 'unknown')

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

Example:

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

Returns true if the value is null.

Example:

• isnull(phone)

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

Examples:

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

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

Example:

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

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

Examples:

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

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)

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

Examples:

• contains('broomstick', 'room') = true
• contains(product, 'trial version')

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

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)

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

Examples:

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

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

Examples:

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

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)

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)

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)

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)

Returns the length of the given text.

Examples:

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

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

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)