Currently, there is no way directly to reference the value of a field in one record from another record, making such a calculation as
{'8 January'→Total} - {'1 January'→Total}
impossible.[1]
However, it is possible to reference such values indirectly, through a linked record.
Possible — but not very pretty.
With that caveat, here is a demo base that does what you requested. (Essentially, it implements version 2.0 of an algorithm I outlined in this earlier post.) Since it is based on linked records, it requires you to link each newly created record to a record in another table; however, as the second table contains only a single record, doing so adds only two mouse-clicks to the data entry process.
Note: The implementation shown in the demo base is very specific to the example you gave in that it is based on the cardinal week of the year, uses a text string in the format of “Day# MonthName” as the primary field, and so forth. However, it should be relatively easy to adapt the routines to support other methods of differentiating between current and previous measurements.
It’s probably best to start by looking at the base. (You’ll want to copy the base so you can examine and modify field configurations.)
You will see I have defined four new fields in [Table 1] — {Link to Calc}, {FullDate},[2] {WeekCount}, and {PreviousWeek} — and a new table, [Calc]. (In use, {FullDate}, {WeekCount}, and {PreviousWeek} would typically be hidden.)
Upon creating a new record in [Table 1], three fields must be completed:
-
{Date}. A string in the format “Day# MonthName” — “1 January”, “15 February”, and the like.
-
{Total}. The total number of people registered as of that date.
-
{Link to Calc}. A link to the [Calc] table.
To create such a link, click on the plus sign ('+') in the {Link to Calc} field. Select the sole record in [Calc], named, in this case, '✅' — the Heavy White Check Mark emoji. This provides an immediate visual confirmation the record has been properly linked.
Once these three fields have been created, a value is defined for {WeekCount} consisting of the cardinal week of the year, formatted in double digits, followed by a colon (':'), and ending with {Total} — like so:
12:345
In the [Calc] table, the {Counts} field rolls up all {WeekCount} values into a single string, with the vertical bar character ('|') as separator. In addition, a vertical bar is prepended and appended to the concatenated string:
|01:20|02:23|03:36|04:63|05:118|06:394|07:437|08:475|09:490|
And, now, things get a little complicated. :winking_face:
In [Table 1], the {PreviousWeek} field in turn rolls up {Calc::Counts} using the following aggregation formula:
IF(
AND(
Date,
Total
),
VALUE(
MID(
ARRAYJOIN(values),
FIND(
'|'&
DATETIME_FORMAT(
DATEADD(
FullDate,
-7,
'days'
),
'ww'
),
ARRAYJOIN(values)
)+4,
FIND(
'|',
ARRAYJOIN(values),
FIND(
'|'&
DATETIME_FORMAT(
DATEADD(
FullDate,
-7,
'days'
),
'ww'
),
ARRAYJOIN(values)
)+1
)-(FIND(
'|'&
DATETIME_FORMAT(
DATEADD(
FullDate,
-7,
'days'
),
'ww'
),
ARRAYJOIN(values)
)+4
)
)
)
)
In brief, that searches {Calc::Counts} for the {WeekCount} entry for the previous week and extracts the previous week’s {Total}.
Once {PreviousWeek} is determined, the calculations for {Increase} and {% Increase} are straightforward.
Well, I said it wasn’t pretty…
Actually, while it may look a bit daunting at first, the process is really quite simple; even better, by varying the method used to differentiate between current and previous values, it can be applied to a wide range of problems.
If any of this isn’t clear — ahem — please don’t hesitate to ask for further explanation!
. ----------
- Please note the representation I’m using here —
{'1 January'→Total}, meaning "the value of {Total} in the record where the primary field is equal to '1 January'" — is of my own invention and completely invalid.
-
{FullDate} simply converts such a string as "1 January" to an Airtable-standard Date field to conform to the record structure given in the original post; it is not a part of the current/previous calculation.
