Formula Reference
- Table of Contents
- Introduction
- Operators
- Conditions
- Arrays
- Locations
- Math
- Numbers
- Text
- Time
Below you will find information about how to use functions in calculated fields within iSENSE.
(l: number) + (r: number) -> number
Returns l added to r.
(l: text) + (r: number) -> text
Concatenates the text value l with the textual representation of r.
The arguments l and r can be reversed.
If the arguments are reversed, the order of concatenation is reversed.
(l: text) + (r: text) -> text
Concatenates the two text values l and r.
(l: timestamp) + (r: number) -> timestamp
Increments the timestamp l by r seconds.
The arguments l and r can be reversed.
(l: number) - (r: number) -> number
Returns l minus r.
(l: location) - (r: location) -> location
Returns the distance between the two locations l and r as a percentage of the radius.
For example, to obtain an arc length in miles, multiply this value by the radius of the Earth in miles.
(l: timestamp) - (r: timestamp) -> number
Returns the number of seconds between l and r.
(l: number) * (r: number) -> number
Returns l multiplied by r.
(l: text) * (r: number) -> text
Returns the text l repeated r times.
If r is a fractional value, that percentage of characters are repeated.
If r is negative, the string is reversed.
The arguments l and r can be reversed.
(l: number) / (r: number) -> number
Returns l divided by r.
(l: number) % (r: number) -> number
Returns
l modulo
r.
For an explanation of the modulo operation, refer to
this.
(l: number) ^ (r: number) -> number
Returns l raised to the power of r.
(l: bool) && (r: bool) -> bool
If both l and r evaluate to true, the expression evaluates to true.
Otherwise, it evaluates to false.
(l: bool) || (r: bool) -> bool
If either l or r evaluate to true, the expression evaluates to true.
Otherwise, it evaluates to false.
(l: any) == (r: any) -> bool
Both l and r must be the same type, and must be comparable.
The expression evaluates to true if l is equal to r.
(l: any) != (r: any) -> bool
Both l and r must be the same type, and must be comparable.
The expression evaluates to true if l is not equal to r.
(l: any) < (r: any) -> bool
Both l and r must be the same type, and must be orderable.
The expression evaluates to true if l is less than r.
(l: any) <= (r: any) -> bool
Both l and r must be the same type, and must be orderable.
The expression evaluates to true if l is less than or equal to r.
(l: any) > (r: any) -> bool
Both l and r must be the same type, and must be orderable.
The expression evaluates to true if l is greater than r.
(l: any) >= (r: any) -> bool
Both l and r must be the same type, and must be orderable.
The expression evaluates to true if l is greater than or equal to r.
If x is true, the expression evaluates to false.
Otherwise, it evaluates to true.
if(cond: bool, then: any, else: any) -> any
Takes an expression that evaluates to true or false. If that expression evaluates to true, then is
returned. Otherwise, else is returned. Both then and else must evaluate to
the same type. The type returned is therefore the same as both then and else.
A constant defined as the boolean value "true".
A constant defined as the boolean value "false".
bool(x: number or bool) -> bool
Converts x from either a number or a boolean to a boolean. If x is a number,
bool returns false if x is either 0 or nothing. Otherwise, it returns true. If
x is a boolean, x is returned without any changes.
default(array: [any], default: any) -> [any]
Takes an array and returns a new array with the default value set to default.
The value for default must match the contents of array.
next(array: [any], default: any) -> [any]
Takes an array and returns a new array with the position incremented by one.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
next_p(array: [any], idx: number, default: any) -> [any]
Takes an array and returns a new array with the position incremented by idx.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
prev(array: [any], default: any) -> [any]
Takes an array and returns a new array with the position decremented by one.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
prev_p(array: [any], idx: number, default: any) -> [any]
Takes an array and returns a new array with the position decremented by idx.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
first(array: [any], default: any) -> [any]
Takes an array and returns a new array with the position set to 0.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
first_p(array: [any], idx: number, default: any) -> [any]
Takes an array and returns a new array with the position set to idx.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
last(array: [any], default: any) -> [any]
Takes an array and returns a new array with the position set to array_length(array) - 1.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
last_p(array: [any], idx: number, default: any) -> [any]
Takes an array and returns a new array with the position set to array_length(array) - idx - 1.
If default is provided, then the default value for array is set to it.
Otherwise, it is left unchanged.
The value for default must match the contents of array, and is optional.
array_length(array: [any]) -> number
Takes an array and returns the number of elements in the array.
latitude(x: number or text or latitude) -> latitude
Converts x to a latitude.
If x is a number, it is interpreted as a latitude in degrees.
If x is text, it is parsed as a number and interpreted as a latitude in degrees.
If x is a latitude, it is returned without any changes.
longitude(x: number or text or longitude) -> longitude
Converts x to a longitude.
If x is a number, it is interpreted as a longitude in degrees.
If x is text, it is parsed as a number and interpreted as a longitude in degrees.
If x is a longitude, it is returned without any changes.
location(x: longitude, y: latitude) -> location
Converts a longitude and latitude pair to a location.
degrees(x: longitude or latitude) -> number
Converts a latitude or longitude to an angle represented in degrees.
radians(x: longitude or latitude) -> number
Converts a latitude or longitude to an angle represented in radians.
A numeric constant defined as Euler's number, or 2.71828...
A numeric constant defined as pi, or 3.14159...
sqrt(n: number) -> number
Computes the square root of n.
If n is less than zero, the function returns a nothing value.
Computes the absolute value of n.
floor(n: number) -> number
Rounds the number n down to the next integer value.
ceil(n: number) -> number
Rounds the number n up to the next integer value.
round(n: number) -> number
Rounds the number n to the closest integer value.
Returns the sine of n as an angle in radians.
Returns the cosine of n as an angle in radians.
Returns the tangent of n as an angle in radians.
sinh(n: number) -> number
Returns the hyperbolic sine of n as an angle in radians.
cosh(n: number) -> number
Returns the hyperbolic cosine of n as an angle in radians.
tanh(n: number) -> number
Returns the hyperbolic tangent of n as an angle in radians.
asin(n: number) -> number
Returns the inverse sine of n as an angle in radians.
acos(n: number) -> number
Returns the inverse cosine of n as an angle in radians.
atan(n: number) -> number
Returns the inverse tangent of n as an angle in radians.
asinh(n: number) -> number
Returns the inverse hyperbolic sine of n as an angle in radians.
acosh(n: number) -> number
Returns the inverse hyperbolic cosine of n as an angle in radians.
atanh(n: number) -> number
Returns the inverse hyperbolic tangent of n as an angle in radians.
Returns the natural logarithm of n.
If n is less than or equal to 0, the function returns a nothing value.
log2(n: number) -> number
Returns the logarithm base 2 of n.
If n is less than or equal to 0, the function returns a nothing value.
log10(n: number) -> number
Returns the logarithm base 10 of n.
If n is less than or equal to 0, the function returns a nothing value.
atan2(y: number, x: number) -> number
Computes the inverse tangent given an x and y.
Unlike atan, this function is capable of returning an angle in radians in the correct quadrant.
log(a: number, b: number) -> number
Returns the logarithm base b of a.
If a is less than or equal to 0, the function returns a nothing value.
If b is less than or equal to 1, the function returns a nothing value.
min(a: number, b: number) -> number
Returns the minimum value between a and b.
If only one of the two arguments are nothing values, the non-nothing value of the two is returned.
If both are nothing values, a nothing value is returned.
max(a: number, b: number) -> number
Returns the maximum value between a and b.
If only one of the two arguments are nothing values, the non-nothing value of the two is returned.
If both are nothing values, a nothing value is returned.
sum(a: [number]) -> number
Computes a sum over the number array a.
Nothing values in the array are treated as zero.
An empty array has a sum of zero.
prod(a: [number]) -> number
Computes a product over the number array a.
Nothing values in the array are treated as one.
An empty array has a product of one.
mean(a: [number]) -> number
Computes the mean over the number array a.
Nothing values in the array are treated as zero.
An empty array has a mean of zero.
variance(a: [number]) -> number
Computes the variance over the number array a.
Nothing values in the array are treated as zero.
An empty array has a variance of zero.
stddev(a: [number]) -> number
Computes the standard deviation over the number array a.
Nothing values in the array are treated as zero.
An empty array has a standard deviation of zero.
array_min(a: [number]) -> number
Determines the minimum value in the number array a.
Nothing values are ignored in the computation.
If a is empty, a nothing value is returned.
array_max(a: [number]) -> number
Determines the maximum value in the number array a.
Nothing values are ignored in the computation.
If a is empty, a nothing value is returned.
number(x: bool or number or text or timestamp or latitude or longitude) -> number
Converts the value x into a number.
If x is a boolean, it is converted to one if true and zero if false.
If x is a number, it is returned as-is.
If x is text, it is parsed as a number.
If the parse fails, the nothing value is returned.
If x is a timestamp, the number of seconds since the Unix epoch is returned.
If x is a latitude or longitude, the value in degrees is returned.
strlen(t: text) -> number
Returns the length of the text t.
capitalize(t: text) -> text
Returns t with the first character capitalized.
Returns t with every character capitalized.
Returns t with every character made lowercase.
Returns t with the case of every character swapped.
This means that all lowercase characters are made uppercase, and all uppercase characters are made lowercase.
Removes whitespace from the edges of t.
substr(t: text, spos: number, epos: number) -> text
Takes the substring of t, starting at spos and ending before epos.
If spos is omitted or refers to a location before the end of, it defaults to the first character.
If epos is omitted or refers to a location after the end of t, it defaults to the last character.
text(x: bool or number or text or timestamp or latitude or longitude or location) -> text
Converts the value x into text.
datetime(yr: number, mo: number, dy: number, hr: number, mn: number, sc: number) -> timestamp
Creates a timestamp from the given arguments.
yr refers the current year. Defaults to 0.
mo refers to the month in the year, starting at 1 for January. Defaults to 1.
dy refers to the day of the month, starting at 1 for the first of the month. Defaults to 1.
hr refers to the hour of the day. Defaults to 0.
mn refers to the minute of the hour. Defaults to 0.
sc refers to the second of the minute. Defaults to 0.
Any value outside of the range for any of the arguments will change the next largest unit of time.
For example, setting the month to 13 will increment the year by one.
Every argument in this functional is optional.
If an argument is omitted or receives the nothing value, it is set to its default.
offset(t: timestamp, off: number, units: text) -> text
Offsets the timestamp t by off.
units determines what measure of time is meant by off.
If it is not provided, it defaults to seconds.
Valid values for units are: second(s), minute(s), hour(s), day(s), week(s), month(s), year(s).
If an invalid unit of time is provided, no change to the time is made.
elapsed(l: timestamp, r: timestamp, units: text) -> text
Determines how much time has elapsed between l and r.
Follows the same rules for units as offset.
second(t: timestamp) -> number
Returns the number of seconds since the last minute in t.
minute(t: timestamp) -> number
Returns the number of minutes since the last hour in t.
hour(t: timestamp) -> number
Returns the number of hours since the last day in t.
day(t: timestamp) -> number
Returns the day of the month in t.
week(t: timestamp) -> number
Returns the week of the year in t.
weekday(t: timestamp) -> number
Returns the day of the week in t.
month(t: timestamp) -> number
Returns the month of the year in t.
Months start at 1 and end at 12.
year(t: timestamp) -> number
Returns the year.
am_pm(t: timestamp) -> text
Returns the text "AM" if t is an AM time, or "PM" if it is a PM time.
weekday_name(t: timestamp) -> text
Returns the name of the weekday in t.
month_name(t: timestamp) -> text
Returns the name of the month in t.