Data Feed Formula Reference

Every column of a data feed has a formula: an
expression that produces that column's values from the feed's raw data. The
simplest formula is a bare reference to one source field; anything Excel-like
can be built on top of it.

{ "name": "Amount",   "type": "NUMERIC", "formula": "__source__@C:C;" }
{ "name": "Category", "type": "TEXT",    "formula": "IF(__source__@C:C; > 1000, \"Large\", \"Small\")" }

The language looks like Excel and shares most of its function names, but it is
not Excel. It operates on whole columns rather than cells, it has no & or
^ operators, and several familiar names (REPLACE, TRIM, DATE) do
something different from their Excel counterparts. Those differences are called
out throughout, and collected in Traps.

Writing a formula is a normal PATCH /data_feeds/{id} on the columns array.
A write replaces the whole array, so send every column you want to keep — see
Data feeds.

The shape of a formula

• A leading = is optional. =SUM(...) and SUM(...) parse identically.
• Function names are case-insensitive. if(...), If(...) and IF(...)
  are the same function. This guide uppercases them by convention.
• Arguments are separated by commas.
• Whitespace and newlines between tokens are ignored, so a long nested formula
  can be laid out over several lines. (Remember to escape the newlines when you
  put the formula in JSON.)
• // starts a line comment; /* ... */ is a block comment.

IF(__source__@F:F; < 10, "A: 1-9",
IF(BETWEEN(__source__@F:F;, 10, 49), "B: 10-49",
IF(BETWEEN(__source__@F:F;, 50, 249), "C: 50-249",
BLANK())))

Everything is a vector

This is the one idea that makes the rest of the language make sense, and it is
where an Excel mental model goes wrong first.

A formula is evaluated once for the whole column, not once per row. Every
value in the language is a vector — an ordered list of values. A reference
like __source__@C:C; is the entire source field as one vector. A literal like
10 is a vector of length one, which the language calls a scalar.

Operators and most functions work element-wise:

When two vectors of different lengths meet in an arithmetic operator, the result
has the length of the longer one and the missing elements are treated as 0.
A scalar on either side is applied to every element of the other.

The practical consequence: a column formula should produce a vector of the same
length as the feed's other columns. An aggregate like SUM or COUNT returns a
single value, and a column whose formula is an aggregate will have one row.
To keep a per-row result while aggregating, use GROUPBY (below), not SUM.

Referring to data

There are two kinds of reference, and they use different punctuation. Both end
in a semicolon. Forgetting it is the single most common syntax error: the
lexer reads everything from the @ or # to the next ; as the address, so an
unterminated reference swallows the rest of the formula.

The feed's own source: __source__@<address>;

__source__ is a placeholder the public API substitutes for the feed's internal
datasource id, in both directions — read a feed and you get __source__, write
__source__ back and it is resolved for you. Write the placeholder literally;
never try to guess the real id.

What goes after the @ depends on the format of the source (source.format
on the feed), not on the connector:

The Excel-style forms are:

The json and xml form is large enough to have its own section.

An address may contain commas (the Sheet1,C:C form always does). They do
not split function arguments — the address runs to the ;, so
IF(__source__@Sheet1,C:C; > 8, "yes", "no") is a three-argument IF, not a
four-argument one.

Another column: <feedId>#<columnId>;

A reference of the form <feed id>#<column id>; reads an already-computed
column, either of this feed or of another one. Both ids are the public ids
returned by GET /data_feeds.

// this feed's own "Rating" column, by id
2b7f911633132df974e331a8f7e9fb9c#44da6dd61efae626819f61f0a171999b;

// a column of a different feed this one is joined to
733715a57c24cf19eb7a90fe4dc114fc#f210d320aaeecb09cf40eafa733161ff;

Use this to build a column on top of another column's result instead of
repeating its formula. Referencing another feed only resolves if the two feeds
are joined — see joined_to_ids on the feed.

Unlike __source__, these ids are not rewritten by the API. Write the real
feed id and column id.

Addressing JSON and XML

When source.format is json or xml, the address after the @ is an XPath
expression rather than a cell range. JSON is treated as the same kind of
hierarchical document as XML: objects become elements named by their keys,
arrays become repeated sibling elements. So a JSON document like

{ "player": [
    { "first_name": "Anders", "last_name": "Hedberg", "team": { "name": "Winnipeg", "wha": "1974-1978" } },
    { "first_name": "Maurice", "last_name": "Richard", "nickname": "Rocket", "team": { "name": "Montreal", "nhl": "1942-1960" } }
] }

is addressed as /player/first_name, /player/team/wha, and so on.

Two things to keep straight before anything else:

• XPath indexes are 1-based. /player[1] is the first player. (The formula
  language's own SUBSTRING and SLICE are 0-based — they are unrelated.)
• @ inside the path is not the datasource separator. Some sources use @
  as the first character of a field name, and in XML @ prefixes an attribute.
  Only the first @ in the formula separates the datasource from its address.

Choosing how much to select

The same element can be addressed several ways, and the choice decides how many
values the column gets. These are the forms the modeller's Selection Options
generate, and the ones worth knowing by hand:

Predicates

A predicate in square brackets filters the nodes at that step.

// last names of players on team Winnipeg
/player[team/name='Winnipeg']/last_name

// every value whose field NAME contains "name"
/player/*[contains(name(),'name')]

// first names of players that have a nickname at all
/player[nickname]/first_name

Standard XPath functions are available inside a predicate; the ones that earn
their keep here are name(), contains(), substring(), position(),
last() and count().

Axes

Axes select relative to the current node — child::, descendant::,
following-sibling::, preceding-sibling:::

// first names of players that have a nickname sibling
/player/first_name[following-sibling::nickname]

// last names of players who played in the WHA
/player/last_name[following-sibling::team/wha]

One JSON-specific catch: sibling order in JSON is alphabetical by field name,
not document order. (In XML it is document order.) So whether a field is a
preceding-sibling or a following-sibling of another follows from their names,
not from how the JSON was written — first_name precedes last_name because
f sorts before l, regardless of the order the service emitted them in.

The alignment problem

This is the failure mode that actually bites, and it follows directly from
everything being a vector: a feed's columns are
combined by position, so every column must produce the same number of values
in the same order. A field that is missing from some records silently breaks
that. Given the example above, where only the second player has a nickname:

/player/first_name   ->  Anders, Maurice     (2 values)
/player/nickname     ->  Rocket              (1 value)

The feed lines Rocket up with Anders. Nothing errors; the data is just
wrong. kf:fill_elements(path, 'fieldname') fixes it by filling the gap
wherever the field is absent:

kf:fill_elements(/player,'nickname')  ->  "", Rocket

Reach for it whenever a JSON column comes from an optional field. Two caveats:

• The filler is an empty string, not a null, so a row it padded is caught by
  = "" and not by = BLANK(). See
  Blanks.
• It does not work for nested arrays.

The kf: functions

kf:names is the common one, because APIs routinely return data keyed by the
thing you want on an axis — a date, a country, a demographic bucket:

kf:names(/player[1])        ->  first_name, last_name, team
kf:names(/player/team)      ->  name, wha, name, nhl
kf:element_at(/rows,1)      ->  the 1st value of each row, where rows are unnamed

Watch the sort when pairing names with values. kf:names sorts its result
alphabetically unless you pass false, while the matching values come back in
source order. So the obvious-looking pair

__source__@kf:names(/data/values/value);   // the categories — SORTED
__source__@/data/values/value/*;           // the numbers  — source order

only lines up if the keys happened to already be in alphabetical order. Pass
false to keep both sides in the same order:

__source__@kf:names(/data/values/value, false);
__source__@/data/values/value/*;

Literals

Operators

From loosest to tightest binding:

Parentheses group as usual.

Note what is missing, because Excel habits will reach for all three:

• No & for string concatenation — use CONCAT(a, b).
• No ^ for exponentiation — use POWER(x, n).
• No % for modulo — use MOD(x, n). (% is also not a percent suffix.)
• No <> for inequality — it is spelled !=.

= is equality. A leading = at the very start of the formula is the optional
prefix, not a comparison.

Blanks, nulls and empty strings

Three things look similar and are not:

• BLANK() returns a vector of one null value. BLANK(n) returns n of
  them. It is the normal way to say "no value" in a result: the FALSE branch
  of an IF that should leave the cell empty is BLANK().
• null is the literal null value. x = BLANK() and x = null both test
  for emptiness.
• "" is an empty string, which is a value. It is not equal to BLANK().

Blanks propagate: most functions return a blank for a blank input rather than an
error. The counting functions differ deliberately: COUNT skips both blanks and
empty strings, COUNTBLANK counts only those, COUNTNUMERIC counts only the
elements that are numbers, and COUNTALL counts every element whatever it is.

Types and coercion

There are three value types — number, string and logical — plus an error type.
A vector has one type for all of its elements.

Functions convert their arguments to the type they need, so an explicit cast is
rarely necessary — "10" + 5 is 15. Comparison between a string and a number
compares them numerically where both parse as numbers, and as text otherwise.

Note that a column's declared type (TEXT, NUMERIC, DATE) is applied to
the formula's result, and a DATE column additionally needs fmtArgs saying
how to read and render its values. The formula language itself has no date
type — see Dates.

Function catalogue

Names are case-insensitive. [opt] marks an optional argument.

This is the complete set of supported functions — the same list the app's
formula bar offers. The evaluator also resolves a number of internal names that
exist for the product's own use; they are not supported, not documented, and may
change or disappear without notice. If a function is not listed below, do not
use it.

Logic and conditionals

Selecting and reshaping vectors

Aggregation

All of these collapse a vector to a scalar.

SUM, SUMIF, AVERAGE, AVERAGEIF, MEDIAN, MODE, MIN, MAX,
COUNT, COUNTALL, COUNTNUMERIC, COUNTBLANK, COUNTIF, STDEV,
STDEVP, VARIANCE, VARIANCEP, RANK, SLOPE, STANDARDIZE,
NORMSDIST, ZTEST, ERF, ERFC.

COUNTIF and SUMIF take a condition vector rather than Excel's criteria
string: COUNTIF(__source__@A:A; <= 30).

GROUPBY(keys, values, [aggregation]) is the one that keeps a row per
group. It buckets values by the matching element of keys and aggregates each
bucket. The third argument is a formula string in which the variable
values is bound to the bucket — it defaults to "SUM(values)", and anything
is allowed there:

GROUPBY(__source__@A:A;, __source__@B:B;)                        // sum per key
GROUPBY(__source__@A:A;, __source__@B:B;, "AVERAGE(values)")
GROUPBY(__source__@A:A;, __source__@B:B;, "COUNTDISTINCT(values)")
GROUPBY(__source__@A:A;, __source__@B:B;, "JOIN(values)")

Results are in alphabetical order of keys, so they align with GROUP(keys) —
which is how you build the label column beside an aggregated one.

Running totals and trends

Text

Two names do not mean what Excel means:

• REPLACE(v, old, [new]) replaces whole values that equal old, not
  substrings at a position. For substrings use SUBSTITUTE.
• TRIM(v, [match]) removes elements from the vector — those equal to
  match, or all blanks if match is omitted. It does not touch whitespace;
  that is TRIM_WHITESPACE.

Math

ABS, ROUND(x, digits), CEILING(x, [significance]),
FLOOR(x, [significance]), MOD(x, n), POWER(x, n).

CEILING and FLOOR round to a multiple (CEILING(0.234, 0.01) is 0.24),
where ROUND's second argument is a number of decimal places.

Lookup

LOOKUP(values, keys, results) — for each element of values, find it in
keys and return the matching element of results. keys and results are
typically columns of a joined feed:

LOOKUP(__source__@A:A;,
       733715a57c24cf19eb7a90fe4dc114fc#641b58722863c003fefeb7861177b9b6;,
       733715a57c24cf19eb7a90fe4dc114fc#f210d320aaeecb09cf40eafa733161ff;)

DATASOURCE(datasourceId, pointer) reads a field of another data source by
literal id — DATASOURCE("0123…def", "A:A"). The public API does not expose
datasource ids (that is the whole point of __source__), so prefer
<feedId>#<columnId>; for cross-feed reads; this is here because you will see it
in formulas written in the app.

Variables and mapping

SET(names, values, expression) binds variables for one expression:

SET(ARRAY("$low", "$high"), ARRAY(10, 100),
    SELECT(__source__@A:A;, BETWEEN(__source__@A:A;, $low, $high)))

MAP(values, varName, expression) evaluates expression once per element
of values, with varName bound to that element:

MAP(__source__@A:A;, "$it", $it / 10)

MAPFLAT is the same but flattens a per-element vector result into one vector.
Variable names start with $, and the name is passed as a string in the
second argument while the expression uses it as a bare $name.

Dates

A date in this language is a number: epoch seconds. There is no date type.
So a date pipeline is always: parse text into epoch seconds, do arithmetic, then
format back to text.

DATE(__source__@B:B;, "yyyy-MM-dd")          // text  -> epoch seconds
DATE_ADD(<epoch>, "3", -1)                   // epoch -> epoch (one month earlier)
DATEVALUE(<epoch>, "MMM yyyy")               // epoch -> text

Unit codes

Units are passed as numbers (usually quoted), not names. This table is not
guessable, and getting it wrong is silent — DATE_ADD(d, "5", 1) adds a day,
DATE_ADD(d, "3", 1) adds a month.

DATE_ADD, DATE_STARTOF/DATE_ENDOF and DATE_IN accept the calendar units
(1–8); DATE_UNITVALUE additionally accepts 15, 34 and 45.

The first argument

Several date functions take a first argument naming where a period starts: a
day of week for weeks ("sun", "mon", "tue", "wed", "thu", "fri",
"sat") or a month for quarters and fiscal years ("jan" … "dec"). Omit it —
or pass null or "" — for the account default.

Formats and time zones

format is a Java SimpleDateFormat pattern (yyyy-MM-dd, dd-MMM-yy,
MM/dd/yyyy HH:mm:ss). Two special values are recognised: "EPOCH" and
"EPOCH-MS", for input that is already epoch seconds or milliseconds.

timezone is an IANA zone id ("America/Toronto", "UTC", "GMT+8"). Omit it
to use the account's time zone. Where a function has both a per-date time zone
(on DATE) and a calculation time zone (the trailing argument on
COUNT_DAYS, DATE_ADD and friends), set both to the same value unless you
specifically want them to differ.

Traps

The shortlist of things that are wrong-by-default if you assume Excel or SQL:

1. The trailing ;. Every @ and # reference ends with one. Without it,
   the parser reads to the end of the formula.
2. __source__ is literal. Do not substitute a datasource id; the API does
   that.
3. A formula runs once for the column, not per row. SUM(...) yields a
   one-row column.
4. REPLACE is not substring replacement — that's SUBSTITUTE.
5. TRIM is not whitespace trimming — that's TRIM_WHITESPACE.
6. DATE and DATEVALUE are not Excel's. They parse and format text
   against epoch seconds.
7. Date units are numbers, and "5" is day while "3" is month.
8. SUBSTRING is 0-based and end-exclusive, unlike Excel's MID.
9. SLICE(v) with no start drops the first element, because start
   defaults to 1.
10. No &, ^, % or <> — use CONCAT, POWER, MOD and !=.
11. "" is not BLANK(). Test for emptiness with = BLANK().
12. Commas inside an address do not separate arguments.
13. A JSON field missing from some records misaligns the whole column —
    wrap it in kf:fill_elements (see the alignment problem).
14. XPath indexes are 1-based, while SUBSTRING and SLICE are 0-based.
15. JSON siblings are ordered alphabetically by field name, so
    following-sibling:: follows the names, not the document.
16. kf:names sorts by default. Pair it with a values column using
    kf:names(x, false), or the two columns may not line up.
17. kf:fill_elements pads with "", not a null, so padded rows do not
    match = BLANK().

Worked examples

Bucket a numeric column.

IF(__source__@F:F; < 10, "A: 1-9",
IF(BETWEEN(__source__@F:F;, 10, 49), "B: 10-49",
IF(BETWEEN(__source__@F:F;, 50, 249), "C: 50-249",
IF(__source__@F:F; > 999, "E: 1K+",
BLANK()))))

Fall back to another column when one is empty.

IF(__source__@I:I; = BLANK(), CAPITALIZE(__source__@C:C;), __source__@I:I;)

Normalize a date column to yyyy-MM.

DATE_CONVERT(__source__@B:B;, "d-MMM-yyyy", "yyyy-MM")

Flag rows in the last 30 days.

IF(DATE(__source__@B:B;, "yyyy-MM-dd") >= DATE_ADD(TODAY(), "5", -30), "recent", "older")

Revenue per account, one row per account.

GROUPBY(__source__@A:A;, __source__@G:G;)

Two aligned columns out of a keyed JSON object.

// column 1 — the keys
kf:names(/data/values/value, false)
// column 2 — the values beside them
/data/values/value/*

A JSON field that only some records have.

kf:fill_elements(/player,'nickname')

Enrich from a joined feed.

LOOKUP(__source__@A:A;,
       733715a57c24cf19eb7a90fe4dc114fc#641b58722863c003fefeb7861177b9b6;,
       733715a57c24cf19eb7a90fe4dc114fc#f210d320aaeecb09cf40eafa733161ff;)