Built-in Functions

These built-in functions can be used inside any element that accepts data expressions (text elements, table cells), by enclosing them in curly braces {}.

For example:

Grand total: {SUM(total)}

The formula above uses the built-in SUM function to add up the values in the total data field.

Aggregates

Aggregate functions compute the result of a computation over a range of rows. The first argument is the name of the data field, and the second argument is the path to the array that provides the source rows.

SUM

SUM(fieldName, [domainPath])

Returns the sum of the values in the fieldName field in all rows inside the current section's data source.

COUNT

COUNT(fieldName, [domainPath])

Returns the count of non-null values, including duplicates, in the fieldName field in all rows inside the the current section's data source.

If fieldName is a calculation, returns the number of rows for which the calculation returns true.

COUNTDISTINCT

COUNTDISTINCT(fieldName, [domainPath])

Works the same way as COUNT, but only one occurrence of each distinct value will be counted. For example, the COUNTDISTINCT of a list (A, B, A, B, C, B, C) is 3 (A, B, and C).

AVERAGE

AVERAGE(fieldName, [domainPath])

Returns the average of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

MEDIAN

MEDIAN(fieldName, [domainPath])

Returns the median of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

MIN

MIN(fieldName, [domainPath])

Returns the minimum of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

MAX

MAX(fieldName, [domainPath])

Returns the maximum of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

Other Functions

DATE

DATE()
DATE(dateStr)
DATE(dateStr, formatStr)

If no arguments are provided, returns today's date as a JavaScript date. If a dateStr argument is provided, parses the string to return the corresponding date. By default, the date format 'YYYY-MM-DD' is used for parsing. If the date string is in a different format, the format string may be provided as a second argument.

Examples

{DATE()}
// Returns today's date as a JavaScript date object
{DATE('2017-02-21')}
// Returns the given date, using the default format YYYY-MM-DD
{DATE('2/21/2017', 'M/D/YYYY')}
// Returns the given date, parsed using the specified custom format

FORMAT

FORMAT(value, formatStr)

Formats the given number or date into a string, using the specified format string.

Examples

{FORMAT(DATE(), 'M/D/YY')}
// Returns e.g. "2/21/17"
{FORMAT(1234.5678, '$#,##0.00')}
// Returns "$1,234.57"

NOW

Equivalent to DATE with no arguments. Returns the current date and time as a JavaScript Date.

TODAY

Equivalent to DATE with no arguments. Returns the current date and time as a JavaScript Date.