Formula reference #

Formulas in DataSuite 2 are powered by Math.js with additional string and utility functions. This page covers what’s available – from simple arithmetic to advanced expressions.

Quick start #

For step-by-step scoring examples with real questionnaires, see the questionnaire scoring guide.

A formula is evaluated once per row – think of it as writing a calculation that runs for each participant (row) in your data, using that participant’s values. Reference your selected input variables as v1, v2, v3, etc.:

(v1 + v2) / 2

Use the range syntax v1:v7 with aggregate functions:

sum(v1:v7)

That’s enough for most use cases. Read on for the full reference.

Variables #

Input variables #

When you select variables in the input panel, they are numbered in the order you selected them. For example, if you select Age, Score, and Grade – they become v1, v2, and v3 respectively. The reference badges next to each variable name in the input panel show the assigned numbers.

A blank cell is missing, never 0 – see Missing values for what each kind of expression does with one.

Special variables #

v and c are available when a rule has multiple input variables and the output is set to replace original values. In that case, the formula runs once per variable per row – v is the current cell value and c is the full column. This is useful for applying the same transformation to many variables at once, e.g. v * 100 to convert all selected variables to percentages.

Missing values #

A blank cell reaches the formula as missing – it is never read as 0. What happens next depends on where it is used:

• Arithmetic, comparisons and string functions – the row’s result is missing. (v1 + v2) / 2 with a blank v2 is blank, and so is v1 > 3 ? 1 : 0 – a comparison on a blank cannot decide either way.
• Aggregate functions over a range or a column – the blank is skipped. mean(v1:v7) with one blank item is the mean of the other six, sum(c1) is the sum of the column’s non-blank cells, and count(v1:v7) is how many were answered. When every value is blank, the result is missing (count gives 0). An optional last argument sets the minimum number of valid values: mean(v1:v7, 5) is missing whenever fewer than five items were answered. (quantileSeq is the exception – its second argument is the probability.)
• == – a blank equals nothing, so v1 == 0 is false on a blank cell. Test for one with the guards below, or compare against null: v1 == null.

To branch on a blank or substitute a value:

Sum scores and skipped items: sum(v1:v9) skips a blank item, so a participant who left one question unanswered gets the sum of the other eight – a lower total, not a missing one. To require complete answers, set the minimum: sum(v1:v9, 9). To prorate instead, score by the mean of the answered items and scale it back: mean(v1:v9, 7) * 9 is missing below seven answers and otherwise the sum a fully answered scale would have given at that average.

Values and syntax #

Numbers #

Numbers are written as-is: 42, 3.14, -7. Scientific notation is supported: 1.5e3 (= 1500).

Strings #

Strings are enclosed in double quotes: "hello". Use + to concatenate: "ID_" + v1.

Booleans #

true and false – returned by comparisons and logical operators, and used in conditionals.

Arrays #

Arrays are written with square brackets:

[1, 2, 3]
[v1, v2, v3]
["low", "medium", "high"]

Arrays are important because some functions expect them. For example, min and max can take either separate arguments or an array:

min(v1, v2, v3)
min([v1, v2, v3])

Both forms produce the same result.

The range syntax v1:v7 expands into an array automatically, so sum(v1:v7) becomes sum([v1, v2, v3, v4, v5, v6, v7]) internally and works as expected.

Indexing #

Access individual elements of an array using square brackets. Indices are 1-based (not 0-based like in most programming languages):

c1[1]       // first value in column 1
c1[i]       // current row's value in column 1

Operators #

Arithmetic #

The + operator also concatenates strings: "ID_" + v1 produces "ID_42".

Comparison #

These return true or false. Comparisons work with both numbers and strings.

Logical #

Conditional (ternary) #

condition ? valueIfTrue : valueIfFalse

Example – assign a label based on a score:

v1 >= 70 ? "pass" : "fail"

Nest them for multiple conditions:

v1 >= 90 ? "A" : (v1 >= 70 ? "B" : "C")

Math functions #

These are the most commonly used built-in Math.js functions. For the complete list, see the Math.js documentation.

Basic #

Aggregate #

These work with multiple values or arrays (e.g. via the v1:v7 range syntax). Over a range or a column they skip blank cells, and an optional last argument is the minimum number of valid values – see Missing values:

Trigonometric #

sin(x), cos(x), tan(x), asin(x), acos(x), atan(x) – all in radians.

Custom aggregate functions #

String functions #

All string functions work on text values. Most take a string as the first argument.

Many string functions also support dot syntax – calling the function as a method on the value. Both forms are equivalent:

contains(v1, "test")    // function syntax
v1.contains("test")     // dot syntax – same result

Use whichever reads more naturally. Dot syntax can be especially convenient for chaining: v1.trim().toLowerCase().

Case conversion #

Inspection #

Extraction #

Modification #

Splitting and joining #

Pattern matching #

Formatting #

format(template, arg0, arg1, ...)

Replaces {0}, {1}, etc. with the corresponding arguments, up to five ({0} to {4}):

format("Subject {0}, Group {1}", v1, v2)

Date functions #

Dates in DataSuite 2 are stored as text in ISO 8601 format (YYYY-MM-DD, e.g. "2024-01-15"). The functions below parse those strings and let you compute calendar-aware differences. Invalid date strings produce NaN, which propagates through arithmetic so bad rows surface downstream rather than silently miscompute.

Supported unit values for dateDiff and dateAdd:

• "day" – exact integer days.
• "week" – exact days / 7 for dateDiff; n × 7 days for dateAdd.
• "month" – for dateDiff, completed calendar months plus a fractional remainder of the current month, where the fraction respects the actual length of that month (28/29/30/31). For dateAdd, n is rounded to whole months; the day-of-month is clamped to the destination month’s length (so Jan 31 + 1 month → Feb 28/29).
• "year" – for dateDiff, completed years plus a fractional remainder against the actual year length (365 or 366). For dateAdd, n is rounded to whole years; February 29 of a leap year shifts to February 28 in non-leap target years.

Calendar-aware vs average-length: dateDiff(d1, d2, "month") does not divide by an average month length (such as 30.4375 days). Instead, it counts whole calendar months and treats the leftover days as a fraction of the month they fall in, so anniversary dates always come out exact: dateDiff("2024-01-15", "2025-01-15", "year") is exactly 1.0. The same logic anchors February 29 to February 28 in non-leap years.

Examples:

dateDiff("2024-01-15", "2024-02-15", "month")    // 1.0 – exactly one calendar month
dateDiff("2024-01-15", "2024-03-01", "month")    // ~1.517 – 1 month + 15 days into 29-day Feb
dateDiff("2020-02-29", "2021-02-28", "year")     // 1.0 – Feb 29 anchors to Feb 28
dateDiff("2024-01-15", today(), "day")           // days since enrollment, until now

dateAdd("2024-01-15", 60, "month")               // "2029-01-15" – synthetic 5-year cutoff
dateAdd("2024-01-31", 1, "month")                // "2024-02-29" – clamped to month end
dateAdd("2024-03-01", -30, "day")                // "2024-01-31" – negative n subtracts

Practical example: time-to-event for survival analysis #

A survival dataset typically has an enrollment date and an event date that may be blank when the participant is censored (still under observation). Three transformation rules turn that into the columns the time-to-event analysis module needs:

cutoff = isBlank(event_date) ? today() : event_date
follow_up = dateDiff(consent_date, cutoff, "month")
event_occurred = isNotBlank(event_date)

The first rule picks the event date when present, otherwise treats the participant as still under observation today. The second computes follow-up time in months from enrollment to that cutoff. The third produces the binary event indicator – the role-picker in the analysis module reads false/true as censored/event automatically.

For days or years instead of months, swap the third argument: dateDiff(consent_date, cutoff, "day") or ... "year". If your dataset has a separate last-contact date for participants still alive at the end of follow-up, use that instead of today():

cutoff = isBlank(event_date) ? last_contact_date : event_date

For studies with a fixed administrative cutoff (e.g. 5-year follow-up window), synthesize the cutoff with dateAdd:

admin_cutoff = dateAdd(consent_date, 60, "month")
cutoff = isBlank(event_date) ? admin_cutoff : event_date

Multi-variable formulas #

Use @Name = expression to create multiple output variables in a single rule:

@Total = v1 + v2 + v3
@Average = @Total / 3
@Centered = @Total - (mean(c1) + mean(c2) + mean(c3))

Each @Name creates a new variable (or overwrites an existing one with the same name). Later declarations can reference earlier ones with the @ prefix. The output options panel is disabled for multi-variable formulas since output targets are explicit.

Column arrays are per input variable: mean(c1) is the mean of the first item, and there is no column for a declared variable, so centre a total on the sum of the item means as above rather than on mean(c1 + c2 + c3) – adding columns element-wise fails on the first blank cell in any of them.

Intermediate variables #

Formulas can span multiple lines. Use assignments to break complex calculations into readable steps:

total = v1 + v2 + v3
total / 3

The last line is the result that gets stored. Earlier lines define intermediate variables for use in subsequent expressions. This is equivalent to writing (v1 + v2 + v3) / 3 but easier to read for complex formulas.

Note: intermediate assignments (with =) are different from multi-variable formulas (with @Name =). Intermediate variables exist only within the formula and are not saved to the dataset.

The formula editor #

The formula editor provides several features to help you write formulas:

• Syntax highlighting – numbers, strings, operators, and function calls are color-coded
• Bracket matching – matching parentheses are highlighted when the cursor is next to one
• Autocomplete – start typing (2+ characters) and a suggestion list appears with variable references, functions, and constants. Press Ctrl+Space to open the list manually. Variable suggestions show the display name alongside the reference (e.g. v1 Age)
• Error checking – syntax errors are underlined in the editor with a description in the gutter. Errors are detected as you type
• Search – press Ctrl+F to find text, Ctrl+H to find and replace

Tips #

• A blank is never 0. Arithmetic on a blank cell gives a blank, aggregates skip it – see Missing values. To fill one, coalesce(v1, 0).
• v1 vs c1 – v1 gives you one value (the current row), c1 gives you the entire column as an array. Use c1 for column-wide calculations: v1 - mean(c1) centers each value around the column mean.
• Errors in formulas produce a missing value for that row – the rest of the data is unaffected. Check the data preview to spot any unexpected blanks.
• String concatenation uses +: v1 + " " + v2 joins two text values with a space.
• Parentheses control evaluation order as expected: (v1 + v2) * v3.
• Constants pi, e, Infinity, and NaN are available.
• Comments can be added with #: v1 + v2 # total score. Anything after # on the same line is ignored.