📊 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:
The calculator (top)
The main database (bottom)
Each connect via a relation property.
I automate this connection with the following filter:
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.
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