Database

Advanced Notion Formula: Find Interval Progression

Sarah Brennan ·

advanced-notion-formula-interval-progression-cover.png

📊 Compare Row Values

I wanted to find a way to calculate the difference only between the most recent two entries in a database. Using the formula (highlighted by the red arrow above), I was able to produce the difference automatically as new entries are made. As well (highlighted by the blue arrow above), I found how to capture the percentage increase between the most recent interval and the previous.

How The Databases Are Setup

There are two databases:

  1. The calculator (top)

  2. The main database (bottom)

Screen Shot 2021-02-10 at 11.04.54 AM.png

Each connect via a relation property.

Screen Shot 2021-02-10 at 11.08.17 AM.png

I automate this connection with the following filter:

Screen Shot 2021-02-10 at 11.02.45 AM.png

Next, a rollup called “Views” is made inside the calculator to Total Views from the original database. The formulas will refer to only to this rollup.

Screen Shot 2021-02-10 at 11.16.24 AM.png

Formulas For Interval Progress

These formulas are located in the Calculator Database

Last Interval Formula

if(toNumber(replace(prop("Views"), ".*[,]", "")) - toNumber(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ".*[,]", "")) >= 0, "+" + format(toNumber(replace(prop("Views"), ".*[,]", "")) - toNumber(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ".*[,]", ""))) + " Views", format(toNumber(replace(prop("Views"), ".*[,]", "")) - toNumber(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ".*[,]", ""))) + " Views")

Increase %

floor(1000 * (toNumber(replace(prop("Views"), ".*[,]", "")) - toNumber(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ".*[,]", "")) - (toNumber(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ".*[,]", "")) - toNumber(replace(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ",(?:.(?!,))+$", ""), ".*[,]", "")))) / (toNumber(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ".*[,]", "")) - toNumber(replace(replace(replace(prop("Views"), ",(?:.(?!,))+$", ""), ",(?:.(?!,))+$", ""), ".*[,]", "")))) / 1000

The Formulas In Action

 

Further Reading

Featured
Free Notion Template: One-Click Recurring Checklists

How to create a one-click checklist using Notion. This includes a free template that can be used for personal or professional tasks. The recurring checklist is designed to simplify the process of adding checklists and completing projects, allowing you to focus on what needs to be done rather than spending time recreating repetitive to-do lists.

Free Notion Template: Airline Ticket Organizer for Travel

For those looking for an easy way to manage their travel information, this Notion template for airline tickets can provide a great solution. This template is designed to help users keep track of airline tickets and associated information. It includes sections for collecting and organizing information like flight number, departure and arrival times, airline, layovers, and more.

Free Notion Template: Address List with Map Generator

This address list Notion template is a great tool for organizing places you’re going, places you’ve been, or places to catalog. In this post, we'll look at how the address list can be used to keep track of address information for individuals, businesses, or other places. The template includes properties that describe an address’s name of place, building status, and country, in addition to generating links to Google and Apple maps.

Populate A Weekly Spread From a Notion Calendar

This is a weekly spread between Monday and Sunday that will auto-populate from a Notion calendar. All days are spread into two rows of four columns. For those who like the presentation of a non-database weekly spread but also like the convenience of a database manager, this is the best of both worlds, and it takes a few minutes to setup. There is also the option to duplicate the Notion template below the tutorial.