Formula Editor Documentation

Tuva Staff

December 19, 2017 16:11

Formula examples:

Addition:

<Attribute> + 5: adds 5 to every case value on a given attribute
<Attribute1> or number + <Attribute2> or number + …: Add case values in each row for given attributes
4 + 5: returns constant 9 for each row

Subtraction:

<Attribute> - 5: subtract 5 from a given attribute case value
<Attribute1> or number - <Attribute2> or number - ...: subtract row case values
5 - 4: returns constant 1 for each row

Multiplication:

<Attribute> * 5: multiply attribute row value and 5
<Attribute1> or number * <Attribute2> or number * ...: multiply attribute1 case value and attribute2 case value by row
<Attribute> or number * pi: multiply attribute case value with pi (3.14)

Division:

<Attribute> / 5: divide attribute case value by 5 for each row
<Attribute1> or number / <Attribute2> or number / ...: divide attribute1 case value by attribute2 case value by row

Absolute Value:

abs(<Attribute> or number): absolute value of attribute case value by row or the absolute value of a number
abs(-55.75): returns 55.7

Power:

pow(<Attribute> or number, power): the attribute (or number) is raised to the power value. The power value tells us how many times we'll multiply the base value (attribute or number) with itself.
pow(4, 3): 4*4*4 = 64

Square Root:

sqrt(<Attribute> or number): returns the square root of the attribute value (or number)
sqrt(16): returns 4

Log 10:

log10(<Attribute> or number): returns log of base 10 for each attribute value (or number)
log10(100): returns 2

Natural Log:

log(<Attribute> or number, e): returns the natural log of the attribute value or the natural log of a number.
log(100, e): returns 4.605

Rounding:

round(<Attribute> or number): returns rounding of the attribute or number value
round(5.6): returns 6
round(5.557, 2): rounds the value by 2 decimals, returns 5.56

Modulus:

mod(1,100): returns the modulus (remainder) of an integer division.
mod(52,10): returns 2.

Random:

randomInt(1,100): returns uniform random integer between 1 and 99.
round( random(-1.5, 1.5), 4): returns the uniform random decimal value between -1.5 and 1.5, rounded to 4 decimal places.
pickRandom(["A","B","C"]): returns a random item from list (“A”, “B”, or “C”), each case calculated independently.
For all three functions (randomInt, random, pickRandom), each value in the attribute is calculated independently, so any random value can appear in more than one case. The number of cases will match the maximum number of cases in other attributes.

Logical If:

(<Age> <= 17) ? "child" : "adult" : returns either “child” or “adult” depending on numeric value of age. Equivalent to: if age <= 17, then case value is “child”, else case value is “adult”. In general the expression is (true-or-false-expression) ? (true-result) : (false-result).

Logical Equal, Less Than, Greater Than:

A == B: expression A equal to B (true or false).
A != B: expression A not equal to B (true or false).
A < B: the value of A less than B (true or false).
A <= B: the value of A less than or equal to B (true or false).
A > B: the value of A greater than B (true or false).
A > B: the value of A greater than or equal to B (true or false).
These logical expressions can be used with logical if. Use parentheses around expressions for clarity and order of operations.

Concatenate Text:

concat(<city>, “-”, <state>): returns a combined categorical value such as “Boston-MA”, or “Paris-TX”. There can be two or more parameters to combine, separated by commas: concat("New York" , "," , "NY" , "," , "USA") returns “New York, NY, USA”.

Extract Text:

<attribute>[position]: returns the letter at the given position (1+)
<attribute>[starting-position:ending-position]: returns the substring indicated by the starting position (1+), and ending with the ending position (1+).
“October”[1:3]: returns the substring “Oct” (the 1st-3rd letters).
<gender>[1]: returns the letter “M” or “F” (the 1st letter) from a gender attribute with values “Male” or “Female.
“ABCDEF”[<column-number>]: returns the letter A, B, … F corresponding to the column number 1 through 6.
string(2005)[3:4]: returns “05” from the number 2005 by converting it to a text string (“2005”) and extracting the 3rd and 4th letters.

Text Length:

Length(“October”): returns the length of the text, 7 in this example. The length is the number of letters+symbols+spaces.
<attribute>[Length(<attribute>)]: returns the last letter of the attribute, using a combination of length() and extract [].

Mean:

mean(<Attribute>): returns the mean of the numeric case values of the attribute
Example: mean( <age> ) with age values 1,2,3,3,3 will return 2.4

Median:

median(<Attribute>): median of the numeric case values of the attribute
Example: median( <age> ) with age values 1,2,3,3,3 will return 3

Interquartile Range:

iqr(<Attribute>): interquartile range of the numeric case values of the attribute
Example: iqr( <age> ) with age values 1,2,3,3,3 will return 1

Standard Deviation:

stdDev(<Attribute>): standard deviation of the numeric case values of the attribute
Example: stdDev( <age> ) with age values 1,2,3,3,3 will return 0.894...

Mean Average Deviation:

meanAvDev(<Attribute>): mean average deviation of the numeric case values of the attribute
Example: meanAvDev( <age> ) with age values 1,2,3,3,3 will return 0.74

Variance:

variance(<Attribute>): variance of the numeric case values of the attribute
Example: variance( <age> ) with age values 1,2,3,3,3 will return 0.8

Sum:

sum(<Attribute>): sum of the numeric case values of the attribute
Example: sum( <age> ) with age values 1,2,3,3,3 will return 12

Minimum:

min(<Attribute>): minimum of the numeric case values of the attribute
Example: min( <age> ) with age values 1,2,3,3,3 will return 1

Maximum:

max(<Attribute>): maximum of the numeric case values of the attribute
Example: max( <age> ) with age values 1,2,3,3,3 will return 3

Count:

count(<Attribute>): count of the numeric case values of the attribute
Example: count( <age> ) with age values 1,2,3,3,3 will return 5

Adding New Attributes