Here’s how I’d configure your base to handle by-year summations. (Actually, I’ll toss a small monkey wrench into the works a little later, but for now, let’s stick to the goal of being able to calculate year-to-date expenditures.)
I’ll start with the assumption you want to sum all expenditures rather than keep a running total of each class of expenditure. Should you later decide that is what you want, you’d simply set up a ‘this year’s expense’ field for each class, following these same directions, instead of a single field collecting all classes.
It looks as if your expenditure records already have a date field called, not too surprisingly, {Date}
. My suggested formula — IF(Year={ThisYear},{ExpenditureAmount})
— was actually pseudocode. What you want to do is compare the Year
of each record with the current year, duplicating the expenditure amount on those records that match.
To do so, create a formula field called {Current-Year Expenditures}
. Configure the formula as follows
IF(
IS_SAME(
TODAY(),
{Date},
'year'
),
{Cost}
)
You’ll want to perform the rollup from the [Vehicle]
table — assuming you want expenditures per vehicle. Create a rollup field that follows the {Vehicle}
linked record to rollup all instances of {Current-Year Expenditures}
with an aggregation function of SUM(values)
.
Now, there’s one major problem with this arrangement: It works fine through December 31 — but on January 1, when you might start thinking of writing the total down for tax purposes or the like, the number evaporates as the base starts a new running total.
So why not keep two totals: one for last year and one for this? That way, you can track how well you’re doing in comparison with how well you did last year — and your year’s worth of data doesn’t disappear faster than your New Year’s Eve hangover. In addition to {Current-Year Expenditures}
, create another formula field, {Last-Year Expenditures}
, with this formula:
IF(
YEAR(TODAY())-YEAR({Date})=1,
{Cost}
)
From [Vehicle]
, roll up those values as last year’s total.