Skip to content

Formula functions

This page lists every function you can use in a Formula, with one example each.

Example: if(prop("Steps") > 10000, "Great day", "Keep going") shows “Great day” on a day with 12,400 steps.

How to read this page

  • prop("Name") reads a property of the same entry.
  • An argument with ? is optional.
  • Empty means no value. Most functions give empty, not an error, when a value is missing.
  • A duration counts in minutes.
  • One name for the average: average() works everywhere: on children, on JSON lists and on columns. The older names avg() and mean() still work as other names for it.
  • Entry functions work on one entry. Column functions work on a whole column of values (every entry’s Steps, for example). Where you can use column functions (in Chart blocks, for example) is not decided yet.

Logic

Function Gives Example
if(condition, then, otherwise) one of two values; the other side is never worked out if(prop("Done"), "Kept", "Missed")
empty(value?) true if the value is empty; with no argument, the empty value if(empty(prop("Notes")), "No notes", prop("Notes"))
coalesce(value, more?) the first value that is not empty coalesce(prop("Notes"), "No notes")

Math

Function Gives Example
round(value, digits?) the value rounded to that many decimals (0 by default) round(prop("Sleep score"), 1)
floor(value) rounded down floor(prop("Sleep score"))
ceil(value) rounded up ceil(prop("Sleep score"))
abs(value) the value without its minus sign abs(prop("Weight") - prop("Target weight"))
min(value, more?) the smallest (all numbers or all durations) min(prop("Deep sleep"), prop("REM sleep"))
max(value, more?) the largest max(prop("Deep sleep"), prop("REM sleep"))
clamp(value, min, max) the value kept inside a range clamp(prop("Deep sleep"), 0, 60)
pct(part, whole) part as a percent of whole; an error if whole is 0 pct(prop("Deep sleep"), prop("Total sleep"))
pctChange(new, old) the percent change from old to new; an error if old is 0 pctChange(2030, 1600) gives 26.875
ratio(a, b) a divided by b; an error if b is 0 ratio(prop("Distance"), prop("Minutes"))
bucket(value, width) the start of the band the value falls in bucket(prop("Weight"), 5): 78.4 gives 75

Date and time

Function Gives Example
now() the date and time now dateBetween(now(), prop("Bedtime"), "hours")
today() today at midnight formatDate(today(), "iso")
dateBetween(later, earlier, unit) the gap in "minutes", "hours" or "days" dateBetween(prop("Due back"), today(), "days")
dateAdd(date, amount, unit) the date moved by "minutes", "hours", "days", "weeks", "months" or "years" dateAdd(today(), 7, "days")
isoWeek(date) the week number, 1 to 53 (weeks start on Monday) isoWeek(today())
dayOfYear(date) the day of the year, 1 to 366 dayOfYear(today())
weekday(date) the day of the week, 1 (Monday) to 7 (Sunday) weekday(today()) == 1
formatDate(date, format) the date as text; formats: "iso", "ymd", "mdy", "dmy", "full", "weekday", "monthday", "time", "time24" formatDate(prop("Due back"), "full")

Text

Function Gives Example
concat(text, more?) the texts joined, with no space between concat(prop("First name"), " ", prop("Last name"))
length(text) the number of characters length(prop("Grateful for"))

Children

These total the entries that sit inside this entry. See Children.

Function Gives Example
children(collection) this entry’s children from one collection count(children("Sets"))
sum(children, property) the total of one property of the children sum(children("Sets"), "Weight")
average(children, property) the average, skipping empty values; avg() is another name for it average(children("Sets"), "Reps")
min(children, property) the smallest min(children("Sets"), "Weight")
max(children, property) the largest max(children("Sets"), "Weight")
last(children, property) the last value, in the list’s order last(children("Sets"), "Reps")
count(children) how many children count(children("Sets"))

Functions that total Relations are not decided yet.

JSON lists

These read a list in a JSON property: the points of a route, a heart-rate series. They update live while the property records. The optional field names the value to read in each item.

Function Gives Example
distance(points) the length of a list of points, in km distance(prop("Route"))
duration(list) the time from the first item to the last; paused time does not count duration(prop("Route"))
count(list) how many items count(prop("Heart rate"))
sum(list, field?) the total sum(prop("Steps"), "steps")
average(list, field?) the average average(prop("Heart rate"), "bpm")
min(list, field?) the smallest min(prop("Heart rate"), "bpm")
max(list, field?) the largest max(prop("Heart rate"), "bpm")
first(list, field?) the first item, or its field first(prop("Route"), "time")
last(list, field?) the last item, or its field last(prop("Route"), "time")

Column: summaries

Gaps (empty values) are skipped.

Function Gives Example
sum(column) the total sum(prop("Calories"))
count(column) how many values there are count(prop("Steps"))
average(column) the average; mean() is another name for it average(prop("Steps"))
median(column) the middle value median(prop("Bedtime hour"))
mode(column) the most common value (the lowest one on a tie) mode(prop("Wake hour"))
stddev(column) how spread out the values are; empty below two values stddev(prop("Weight"))
variance(column) the square of stddev variance(prop("Weight"))
percentile(column, p) the value that p percent of values sit below (p from 0 to 100) percentile(prop("Sleep score"), 90)

Column: two columns together

Rows that miss either value are skipped.

Function Gives Example
corr(x, y) how closely two columns move together, -1 to 1 corr(prop("Sleep score"), prop("Steps"))
slope(x, y) how much y changes for each step of x, on the best straight line slope(prop("Day index"), prop("Weight"))
intercept(x, y) where that line crosses x = 0 intercept(prop("Day index"), prop("Weight"))
r2(x, y) how well the line fits, 0 to 1 r2(prop("Day index"), prop("Weight"))

Column: over time

These give a new column of the same length.

Function Gives Example
rollingAvg(column, n) the average of the last n rows; the first n-1 rows are empty rollingAvg(prop("Weight"), 7)
rollingSum(column, n) the total of the last n rows rollingSum(prop("Calories"), 7)
lag(column, n) the value from n rows before lag(prop("Sleep score"), 1)
delta(column) the change from the row before delta(prop("Weight"))
cumsum(column) the running total cumsum(prop("Distance"))
zscore(column) each value as standard deviations from the average zscore(prop("Recovery"))
normalize(column) each value scaled to 0 to 1 between the lowest and highest normalize(prop("Steps"))

Column: streaks

A non-zero value counts as true; zero or empty counts as false.

Function Gives Example
streak(column) the current run of true at the end streak(prop("Hit protein goal"))
longestStreak(column) the longest run of true longestStreak(prop("Hit protein goal"))
longestGap(column) the longest run of false longestGap(prop("Hit protein goal"))