Skip to main content

Using Functions

Maddison Jones
  • Updated

Smartabase has a wide range of built-in functions that simplify the process of writing equations. The functions that are available for use depend on the calculation type. To see the full list of functions for the specific calculation field you're working with, select the Available functions drop-down at the bottom of the Calculation tab and open the tooltip to see details about each function.

A screenshot showing an example of the calculation editor in use.

This article covers functions that you can use in simple (numeric) calculations, option calculations, text calculations, history calculations and table calculations.

Because many functions can be used in different calculation types, this article organizes functions according to their purpose:

Note that Smartabase functions are not yet internationalized and therefore use British English, which means you must use non-American spelling in functions like textsummarise. 

In each example below, the value inside the brackets could be a static value (e.g. 2) or a field name (e.g. Sprint time).

Numeric functions

Numeric functions are for mathematical operations. You can use them across all calculations as long as the arguments for the functions are numeric.

  • abs: returns the absolute value of a number.
abs(-2) = 2
  • ceiling: rounds a number up.
ceiling(12.3) = 13
  • convert: a series of functions that convert a value from one unit of measurement to another. A full list of options can be obtained from the Available functions drop-down in the form builder.
convertcmtokm(10000) = 0.1
  • floor: rounds a number down.
floor(12.7) = 12
  • gt: determines if the first value is greater than the second value. The equation will return 1 if it is true or 0 if it is false. This function can also be achieved with the > operator.
gt(10, 5) = 1
  • gteq: determines if the first value is greater than or equal to the second value. The equation will return 1 if it is true or 0 if it is false. 
gteq(10, 5) = 1
gteq(10, 10) = 1
  • ln: calculates the natural logarithm of a number.
ln(9) = 2.19722...
  • log: calculates the logarithm (base 10) of a number.
log(12) = 1.07918...
  • lt: calculates if the first value is less than the second value. The equation will return 1 if it is true or 0 if it is false. This function can also be achieved with the < operator.
lt(5, 10) = 1
  • lteq: calculates if the first value is less than or equal to the second value. The equation will return 1 if it is true or 0 if it is false.
lteq(5, 5) = 1
lteq(5, 10) = 1
  • mod: calculates the remainder when dividing one number by another.
mod(x, y) where x is divided by y.
mod(9, 2) = 1
  • pow: raises the first value to the power of the second value.
pow(x, y) = xy
pow(2, 3) = 8
  • safe: returns a number value or zero. This can be useful if fields that are part of a calculation might be empty but you still want a result. 
When Strength rating is equal to 3, safe(Strength rating) = 3
When Strength rating is empty, safe(Strength rating) = 0
  • sqrt: calculates the square root of a value.
sqrt(9) = 3
  • sum: calculates the sum of all values. This function can also be achieved with the + operator.
sum(1, 5, 61) = 67
  • sumifpositive: calculates the sum of all the values which are 0 or positive.
sum(1, -2, 3) = 4
  • trunc: returns the closest whole number. It is possible to use trunc to round to the nearest x (x could be 0.5, 2.5, 5, 10, etc.). To do so, divide the number by x and then multiply it by x again as in the second and third examples.
trunc(5.3) = 5
trunc(5.4/0.5) * 0.5 = 5.5
trunc(12.97738/10) * 10 = 10

Statistical functions

Statistical functions are useful when you want to do descriptive statistical analysis. You can use these functions across all calculation types.

The sprint testing examples below assume that the following values are entered for Number fields named Sprint 1 through to Sprint 5. 

Sprint 1 = 4.434 
Sprint 2 = 4.597 
Sprint 3 = 4.561 
Sprint 4 = 4.482 
Sprint 5 = 4.545
  • count: counts the number of items given.
count(5, 3, 2, 9, 19, 24) = 6 
count("football", "cricket", "swimming") = 3
  • countifpositive: counts the number of items given where the values are 0 or positive.
countifpositive(5, 3, -2, 9, 19, 24) = 5
  • max: calculates the maximum value of a range.
max(39, 41, 40, 28, 34) = 41
max(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.597
  • maxifpositive: calculates the maximum value of a range where the values are 0 or positive.
maxifpositive(39, -41, 40, 28, 34) = 40
  • min: calculates the minimum value of a range.
min(120, 124, 119, 134, 129) = 119
min(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.434
  • minifpositive: calculates the minimum value of a range where the values are 0 or positive.
minifpositive(120, 124, -119, 134, 129) = 120
  • mean: calculates the mean value of all values within a range.
 mean(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.5238
  • meanifpositive: calculates the mean value of all values within a range where the values are 0 or positive.
meanifpositive(120, 124, -119, 134, 129) = 126.8
  • median: returns the median value of a range.
median(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.545
  • medianifpositive: returns the median value of a range where the values are 0 or positive.
medianifpositive(2, 4, -3, 6, 8) = 5
  • nthmax: returns the nth largest value of a range, with n being any positive whole number.
nthmax(n, values)
nthmax(2, Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.561
  • nthmaxifpositive: returns the nth largest value of a range where the values are 0 or positive, with n being any positive whole number.
nthmaxifpositive(n, values)
nthmaxifpositive(3, 10, 45, -12, 15) = 10
  • nthmin: returns the nth smallest value of a range, with n being any positive whole number.
nthmin(n, values)
nthmin(2, Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.482
  • nthminifpositive: returns the nth smallest value of a range where the values are 0 or positive, with n being any positive whole number.
nthminifpositive(n, values)
nthminifpositive(2, 10, 45, -12, 15) = 15
  • percentile: returns the kth percentile (i.e. the value below which k% of the data values fall) for a supplied range of values and a supplied k (between 0 and 1, inclusive).
percentile(range, k)
percentile(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5, 0.3) = 4.495
  • percentileifpositive: returns the kth percentile (i.e. the value below which k% of the data values fall) for a supplied range of values and a supplied k (between 0 and 1, inclusive) but ignores any negative values in the data range.
percentileifpositive(range, k)
percentileifpositive(-5, 1, 2, 3, 4, 0.3) = 1.9
  • percentrank: calculates the relative position (between 0 and 1, inclusive) of a specified value within a supplied array.
percentrank(range, value)
percentrank(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5, Sprint 3) = 0.75
  • percentrankifpositive: calculates the relative position (between 0 and 1, inclusive) of a specified value within a supplied array but ignores any negative values in the array.
percentrankifpositive(range, value)
percentrankifpositive(-5, 0, 1, 2, 3, 4, 3) = 0.75
  • sem: calculates the standard error of the mean from a range of values, which is the standard deviation divided by the square root of the number of values. 
sem(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 0.029
  • semifpositive: calculates the standard error of the mean from a range of values, which is the standard deviation divided by the square root of the number of values where the values are 0 or positive. 
semifpositive(10, 45, -12, 15) = 11.7
  • stddev: calculates the standard deviation for a range of values.
stddev(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 0.065
  • stddevifpositive: calculates the standard deviation for a range of values where the values are 0 or positive.
stddevifpositive(10, 45, -12, 15) = 18.9
  • var: calculates the variance of a range of values.
var(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 0.004
  • varifpositive: calculates the variance of a range of values where the values are 0 or positive.
varifpositive(10, 45, -12, 15) = 358.3

Reference functions

Reference functions are essentially look up operations, meaning that they are used to find values using a certain criterion. They can be used in all calculation types as long as the output is consistent with what the calculation type expects. For example, when using a reference type function in a simple (numeric) calculation, the result of the calculation should be a number, not text or a date.

  • firstnonzero: returns the first value from a list of numbers which is not zero.
firstnonzero(0, 0, 3, 2, 4) = 3
  • firstnonzeroifpositive: returns the first value from a list of numbers which is not zero or positive.
firstnonzeroifpositive(0, 0, -2, 3, 4) = 3
  • firstpositive: returns the first positive value from a range.
firstpositive(-2, -3, 2, 6, 7) = 2
  • firstpositiveifpositive: returns the first positive value from a range where the values are 0 or positive.
firstpositiveifpositive(-2, -3, 2, 6, 7) = 2
  • firstvalue: returns the first value from a range.
firstvalue(4, 2, 6, 7, 9) = 4
  • firstvalueifpositive: returns the first value from a range where the values are 0 or positive.
firstvalueifpositive(-1, 4, 2, 6, 7, 9) = 4
  • index: returns a specified item from a range. A positive index starts from the beginning of the range while a negative index runs from the end of a range.
index(index value, range)
index(3, "blue", "orange", "purple", "red", "violet") = purple
index(-2, "blue, "orange") = "blue" since this is the second item from the end of the range.
  • indexifpositive: returns a specified item from a range where the values are 0 or positive.
indexifpositive(index value, range)
indexifpositive(3, 1, 2, -3, 4) = 4
  • lastnonzero: returns the last value from a range that is not zero.
lastnonzero(7, 2, 5, 6, 0) = 6
  • lastnonzeroifpositive: returns the last value from a range that is not zero or negative.
lastnonzeroifpositive(7, 2, 5, 6, -1) = 6
  • lastpositive: returns the last positive value from a range.
lastpositive(4, 6, 1, 3, -5) = 3
  • lastpositiveifpositive: returns the last positive value from a range where the values are 0 or positive.
lastpositiveifpositive(4, 6, 1, 3, -5) = 3
  • lastvalue: returns the last value from a range.
lastvalue(4, 7, 2, 6) = 6
  • lastvalueifpositive: returns the last value from a range where the values are 0 or positive.
lastvalueifpositive(4, 7, 2, 6, -3) = 6
  • row: when used in a table, the row function returns the table row number.
row() = 1, 2, 3...

When used in a table calculation (i.e. a calculation outside the table, about the table contents), the row function can be used to target specific rows. For example, a table calculation with the sumifpositive aggregate could be used to discard Load values in the first two table rows and sum all other positive load values using the following formula:

if(row() > 2, Load, -1)
  • sequentialhistoricalvaluesthatequal: a historical function which returns the number of recent, consecutive historical values that equal a certain value. This function can only be used with history calculations.
sequentialhistoricalvaluesthatequal(n, Historical field) where n is the historical value that you are searching for a match for.
    For example, the following equation searches for the number of consecutive historical records (excluding the current record) where Mood was equal to 1:
sequentialhistoricalvaluesthatequal(1, Historical Mood)
    If Mood in the last three records was equal to 1 then the equation will return 3.
  • valuewhentargetmatches: a historical function which searches for the record containing the result of a specified history calculation and returns the value for a different field from the same record. This function is commonly used when calculating personal bests and can only be used with history calculations.
valuewhentargetmatches(Field of interest, Historical values for the field of interest, Range)

For example, an event form used to collect 100m sprint times contains a Date field named Testing date and a Number field named 100m time. A History calculation named Best 100m time uses the following function to the return the best 100m time for an athlete:

historicalmin(100m time, Historical 100m time)

A Historical date calculation named Date of best 100m searches for the record where the best 100m time occurs and returns the Testing date of that record using the following function:

valuewhentargetmatches(Best 100m time, (Historical 100m time, 100m time), (Historical Testing date, Testing date))

Logical functions

  • and: evaluates n number of statements and returns 1 if all of them are true and 0 if at least one of them is false.
and(5 < 6, 2 > 0, contains("abc", "a"), 2 = 2) = 1
and(2 > 1, 3 < 7, 0 > 4) = 0
  • anyempty: checks whether any of the fields being referenced are empty. If any fields are empty, the function returns a 1, otherwise 0.
When any of the fields referenced are empty, anyempty(Strength, Weakness, Opportunity, Threat) = 1
When none of the fields referenced are empty, anyempty(Strength, Weakness, Opportunity, Threat) = 0
  • contains: determines if the first value contains the second value and returns 1 if true and 0 if false.
contains("Three green pairs of shoes", "green") = 1
contains("Three green pairs of shoes", "red") = 0
  • if: checks whether a statement is true or false and returns pre-defined values for each state.
When Score contains 96, if(Score > 80, "Pass", "Fail") = Pass
When Score contains 31, if(Score > 80, "Pass", "Fail") = Fail
  • isempty: checks whether all of the fields being referenced are empty. If all fields are empty, the function returns a 1, otherwise 0.
When all four fields referenced are empty, isempty(Strength, Weakness, Opportunity, Threat) = 1
When any of fields referenced contain data, isempty(Strength, Weakness, Opportunity, Threat) = 0
  • or: evaluates n number of statements and returns 1 if at least one of them is true or 0 if none of them are true.
When Fatigue contains 5, or(Fatigue > 3, Energy < 3, Motivation < 3) = 1
When Fatigue contains 2, Energy contains 4, Motivation contains 5, or(Fatigue > 3, Energy < 3, Motivation < 3) = 0
  • rawvalue: returns the raw value specified by a field. This function is not commonly used.

Text functions

  • charat: returns the nth character of the specified text, with 0 being the first character.
When Ticket Code contains 234-4532-029450-A, charat(Ticket Code, 1) = 3
  • concatenate: combines all items specified into a single text string, which is useful for summarizing important information.
When Sets contains 3 and Reps contains 12, concatenate(Sets, "x", Reps) = 3x12
  • customtextsummarise: combines all items specified into one text string, using the first item as the divider.
customtextsummarise(" --- ", Motivation, Energy, Fatigue) = High --- Low --- Moderate
  • option: if option fields have scores, Smartabase will use the score in calculations. You can use the option function to use the text result of an option field for the calculation.
When the option called Extreme (scored as 5) for Rating is selected, option(Rating) = Extreme
  • replace: replaces all occurrences of the second value in the first value with the third value. Note that the arguments for the replace function are case sensitive.
replace("Example", "e", "@") = Exampl@
  • replaceall: replaces any occurrences of the third or subsequent values in the first value with the second value.
replaceall("one two three four five six", "zero", "one", "three", "five") = zero two zero four zero six
  • split: splits a specified text using the second value. The third value specifies which occurrence of the second value will be used to split the text. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, split(Ticket Code, "-", 2) = 029450
  • splitfirst: this function works the same way as as split but there is no need to specify a third value. It automatically returns the first segment of the text, like using a a split function with 0. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitfirst(Ticket Code, "-") = 234
  • splitgreaterthan: splits a string of text using the second value and returns everything after the occurrence specified by the third value. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitgreaterthan(Ticket Code, "-", 1) = 4532 029450 A
  • splitlast: same as splitfirst but in this case the function returns the last value. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitlast(Ticket Code, "-") = A
  • splitlessthan: same as splitgreaterthan but in this case it returns the everything before the third value. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitlessthan(Ticket Code, "-", 2) = 234 4532
  • substring: specify the start position (second argument) and number of characters (third argument) to return from a field specified in the first argument. 
When Ticket Code contains 234-4532-029450-A, substring(Ticket Code, 0, 3) = 234
  • textsummarise: combines all items specified into one string of text separated by commas.
When Color contains Blue, Shape contains Square and Size contains Small, textsummarise(Color, Shape, Size) = Blue,Square,Small
  • whitespacetextsummarise: combines all items specified into one string of text separated by blank spaces.
When Color contains Blue, Shape contains Square and Size contains Small, whitespacetextsummarise(Color, Shape, Size) = Blue Square Small

Date and time functions

  • date: returns a date value using the default date format for the Smartabase site.
When Graduation Date contains 05/31/2021 and the site default date format is mm-dd-yyyy, date(Graduation Date) = 05-31-2021
  • dateformat: converts a date value to the specified format.
When Graduation Date contains 05/31/2021, dateformat(Graduation Date, "DDDD") = Monday
  • duration: converts milliseconds to a duration in the specified format.
When Result contains 1326000, duration(Result, "hh:mm:ss") = 00:21:06
  • weekstart: tells you which week of the year a date falls within. However, you must specify which day you consider the week to start on (Sunday = 0, Saturday = 6), which week of the year you want to start counting from (0 - 52) and which week of the year you want the start week to be.
When Graduation Date contains 05/31/2021, weekstart(Graduation Date, 0, 1, 1 ) = 22

Aggregating functions for Table calculations

Table calculations (that is, calculations about the contents of a table in a form) differ from simple calculations in that they use an aggregating function to decide how the data in each table row should be treated. The aggregating function is used in conjunction with the equation entered in the calculation field. You will find that many of the functions described above can be used in the calculation field for table calculations.

A screenshot of the Calculation settings for a Table calculation.

In the example above, the table field called Best 40m will be calculated using the minifpositive aggregate function to return the fastest 40m sprint time from a table that records multiple sprints. Note that the aggregation function options available are different depending on the type of table calculation you are using.

Aggregating functions are based on the functions listed above. The following aggregating functions are available for table calculations:

Table calculations:

Table text and Table option calculations:

Table date calculations:

Table duration calculations:

Historical functions

There are a specific set of functions designed to be used in history calculations. As best practice, use these instead of their regular variations (sum, max, etc.).

However, any function that uses a range of numbers as an argument can be used in a history calculation (i.e. firstvalue and lastvalue). Note that the available functions are different for each type of history calculation (e.g. text functions can be used with History text calculations but not History calculations). 

The custom historical functions are:

  • historicalmax: calculates the maximum historical value for a field.
historicalmax(Historical Bench Press 1RM, Bench Press 1RM)
  • historicalmin: calculates the minimum historical value for a field.
historicalmin(Historical Sprint Time, Sprint Time)
  • historicalmean: calculates the average historical value for a field.
historicalmean(Historical Wellness Score, Wellness Score)
  • historicalsum: calculates the sum of historical values for a field.
historicalsum(Historical Load, Load)
  • historicalstddev: calculates the standard deviation based on historical values for a field.
historicalstddev(Historical Mood, Mood)
  • historicalvar: calculates the variance based on historical values for a field.
historicalvar(Historical Weight, Weight)
  • historicalpercentile: calculates the value below which a given percentage (k) of values for a specific field fall. For example, the 5th percentile (k = 0.05) is the value (or score) below which 5% of the field values may be found.
historicalpercentile(Historical Field name, Field name, k)

For example, the screenshot below shows the Total Wellness Score from the last six wellness records for a person.

A screenshot of a Smartabase report showing Total Wellness Score and History calculation values for some previous records.

The Wellness_5 field is a History calculation with the following equation:

historicalpercentile(Historical Total Wellness Score, Total Wellness Score, 0.05)

The 5th percentile (k = 0.05) for the most recent record is 2.5; that is, a score of 2.5 or lower would be in the lowest 5% of all Total Wellness Score values for this person.

These calculated percentiles can be used to create a flagging system, based on what percentile the athlete's current score sits in.

  • historicalpercentrank: calculates where a value sits as a percent rank in relation to previous values. Ranking works on a 0-1 basis, with one representing 100%. The final element is the field to return the percent rank of, with respect to the first two elements.
historicalpercentrank(Historical Field name, Field Name, Field name)

For example, to find the percent rank of Total Wellness Score, we can calculate it as follows:

historicalpercentrank(Historical Total Wellness Score, Total Wellness Score, Total Wellness Score)

With a Total Wellness Score today of 8, the percent rank would be calculated as 0.8 (i.e. in the 80th percentile of their historical data).

  • historicalewma: calculates the exponentially weighted moving average (EWMA) of a value based on the number of days in the decay period (i.e. the decay constant c) and the event date as a Date calculation. 
historicalewma(Field name, Historical Field name, Date calculation name, Historical Date calculation name, c)

For example, the following function will calculate the EWMA of Total Training Load with a decay constant of 7 days where the form contains a Date calculation named Session Date:

historicalewma(Total Training Load, Historical Total Training Load, Session Date, Historical Session Date, 7)

A date restriction on the data is also required to calculate EWMA over the period of time. The date restriction should include several times more days than the delay constant to ensure more accuracy in the EWMA value.

Related articles