⤴︎ How To Create Every Type Of Rollup With Formulas
The complete guide to creating rollups with formulas 🙌
Hi Notioneers!
I know, it’s been a while, I’m sorry, I’ve been busy making apps lately 🤓 Today’s post is more of a blog post than a newsletter but I’m sending it from Substack to make sure that you get it first.
As I’m sure you’ve heard by now, Notion’s just released a huge upgrade for their formula functionality that’s unlocked so many new opportunities.
An Intro To Notion Formulas 2.0
I recorded a quick summary of the changes and the new things that we can do with formulas now in this video -
I also just wanted to give a shout out to Benjamin Borowski. He’s a true Notion formulas wizard and will be creating lots more resources for formula creators soon.
Creating Rollups With Formulas
One of the most exciting additions to our toolkit is the ability to create rollups with formulas.
In the rest of this post, I’ll share how you can replicate all of the current rollup options with formulas so that you can quickly get started when you need to include rollups in your formulas. If you want to view any of these formulas in a Notion database or with nicer formatting, you can check these out here.
Obviously you can build on these formulas for all sorts of things, like this. But these will just be simple examples or as simple as possible anyway, check out the formula to calculate the median average 😅.
General
Rollups for every type of property.
Show original
map(prop("Tasks"), current.prop("Name"))
Count all
map(prop("Tasks"), current.prop("Name"))
.length()
Text, Numbers & Dates
Rollups for text, number and date properties.
Show unique values
map(prop("Tasks"), current.prop("Name"))
.unique()
Count values
map(prop("Tasks"), current.prop("Name"))
.filter(current)
.length()
Count unique values
map(prop("Tasks"), current.prop("Name"))
.unique()
.length()
Count empty
map(prop("Tasks"), current.prop("Name"))
.filter(current.empty())
.length()
Count not empty
map(prop("Tasks"), current.prop("Name"))
.filter(!current.empty())
.length()
Percent empty
let(
/* Create variable */
taskHours, map(prop("Tasks"), current.prop("Name")),
/* Count empty */
taskHours
.filter(current.empty())
.length()
/
/* Count total */
taskHours
.length()
)
Percent not empty
let(
/* Create variable */
taskHours, map(prop("Tasks"), current.prop("Name")),
/* Count empty */
taskHours
.filter(!current.empty())
.length()
/
/* Count total */
taskHours
.length()
)
Numbers
Rollups based on number properties.
Sum
map(prop("Tasks"), current.prop("Hours"))
.sum()
Average
map(prop("Tasks"), current.prop("Hours"))
.sum()
/
prop("Tasks").length()
Median
let(
sortedHours,
map(prop("Tasks"), current.prop("Hours"))
.sort(),
if(
/* If true then the length of the list is an odd number
so the list contains a middle number */
mod(sortedHours.length(), 2),
/* Get number from the middle of list */
at(
sortedHours,
/* Get index for middle of list */
floor(sortedHours.length() / 2
)
),
/* If false then we need to get the two numbers from the middle of the list
add them together and divide the result by 2 */
lets(
mid1, at(sortedHours, sortedHours.length() / 2),
mid2, at(sortedHours, sortedHours.length() / 2 - 1),
(mid1 + mid2) / 2
)
)
)
Min
map(prop("Tasks"), current.prop("Hours"))
.min()
Max
map(prop("Tasks"), current.prop("Hours"))
.max()
Range
map(prop("Tasks"), current.prop("Hours"))
.max()
-
map(prop("Tasks"), current.prop("Hours"))
.min()
Dates
Earliest date
map(prop("Tasks"), current.prop("Due"))
.sort()
.first()
Latest date
map(prop("Tasks"), current.prop("Due"))
.sort()
.last()
Date range
let(
/* You can change "days" to hours, weeks, months or even years */
timeframe, "days",
dateBetween(
map(prop("Tasks"), current.prop("Due")).sort().last(),
map(prop("Tasks"), current.prop("Due")).sort().first(),
timeframe
)
+ " "
+ timeframe
)
Checkboxes
Rollups based on checkbox properties.
Count checked
map(prop("Tasks"), current.prop("Complete"))
/* You could actually leave the brackets empty here,
as the filter will look for true by default */
.filter(current == true)
.length()
Count unchecked
map(prop("Tasks"), current.prop("Complete"))
/* You could actually leave the brackets empty here,
as the filter will look for true by default */
.filter(current == true)
.length()
Percent checked
/* Uncomment the comments in this formula to round the result to 0 decimal places */
/* round( */
map(prop("Tasks"), current.prop("Complete"))
.filter(current == true)
.length()
/
map(prop("Tasks"), current.prop("Complete"))
.length()
/* * 100) / 100 */
Percent unchecked
/* Uncomment the comments in this formula to round the result to 0 decimal places */
/* round( */
map(prop("Tasks"), current.prop("Complete"))
.filter(current == false)
.length()
/
map(prop("Tasks"), current.prop("Complete"))
.length()
/* * 100) / 100 */
I hope this is a useful reference point for you in the future. If you have any questions, please feel free to reply in the comments.
👋 until next time
Alex aka aNotioneer