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")) |
Read next