Re: Formula for Percentage Increase Without Sequential Dates
Okay, the first problem with your rollup formula in the [Records] table is that it’s rolling up from the {Weights} field in the [Calc] table, but your FIND function in the formula is searching for a number based on {Patient_Record} field, which it will never find. You need to add that number to the weight in a formula field, then roll up that field in the [Calc] table instead of just the direct weights. @W_Vann_Hall mentioned this in his post, but didn’t give a specific example of how to do it, which is why it might’ve been overlooked when you recreated his setup.
In your case, your formula would begin with the {Patient_Record} value (with leading zeroes for a consistent length), a colon, and then the weight, like this:
001:95
There are some other pieces missing from the aggregation formula as well, which I’ll try to fix as I fiddle with it some more.
However, that’s not the only issue I see. Because your [Records] table contains records for more than one patient, you’re going to end up with messy values as you transition from one patient to the next; e.g. from JD1’s last weight in record 27 to JD2’s first weight in record 28. That’s going to be a much more difficult challenge to overcome.
As for sharing bases, click the “Share” button in the upper-right corner of the Airtable UI when looking at your base (or the “Share” link on the base’s contextual menu when viewing your workspaces). In the dialog that opens, change the option under “Shared base link” to “Private read-only link”. The URL you want to share is in the pink box.
Okay, I’ve got a mostly-working solution. The only thing this doesn’t address is the change between patients, and how that throws off the weight change calculation. I also deviated from the setup laid out by @W_Vann_Hall a little bit.
First, add a field to your [Records] table named {Weight Padded}, using the following formula:
REPT(" ", 5-LEN(Weight & "")) & Weight
This prepends a bunch of spaces in front of each weight value, such that the length of the resulting string is always exactly five characters. I chose 5 because I didn’t think you’d need more than that many characters for a given weight: ###.# . However, if you need more, just increase the number, but note it because you’ll need it later on.
In the [Calc] table, modify the {Weights} rollup to pull from {Weight Padded}, using only "values" as the aggregation function.
Back in [Records], use this for the {Previous Weight} rollup aggregation function:
IF(
AND(
Date,
Weight
), VALUE(
MID(
ARRAYJOIN(values,""),
(({Patient_Record} - 2) * 5) + 1,
5
)
)
)
NOTE: if you changed the value from 5 to some other number in the {Weight Padded} formula, replace each “5” in this one with the new number.
Rather than trying to find specific text, this simply uses the {Patient_Record} value to calculate how far into the array-joined mega-string to grab a five-character chunk. That chunk is then turned into a numerical value.
Finally, change the {Change in Weight} formula to this:
IF({Previous Weight}, Weight-{Previous Weight})
And the result looks like this (with most helper fields hidden):
If you want to only show a weight change when there’s one to show, you could use this, which would hide all of the distracting 0.0 entries.
IF(AND({Previous Weight}, Weight-{Previous Weight}), Weight-{Previous Weight})
Add a {Change Date} field to your [Records] table, using the following formula:
IF(Patient_Record=1, Date, IF({Change in Weight}, Date))
This will track the date on the very first record, and on all subsequent records where a weight change is recorded.
In the [Calc] table, add a rollup field named {Change Dates}:
ARRAYUNIQUE will only keep the actual dates, stripping out the blank values.
Back in [Records], add a lookup field named {All Change Dates}:
Finally, add a formula field called {Previous Change} with the following formula:
IF(
NOT(
FIND(
{Change Date},
''&{All Change Dates}
)=1
),DATETIME_FORMAT(
MID(
''&{All Change Dates},
FIND(
{Change Date},
''&{All Change Dates}
)-24,
24
),"MM/DD/YYYY"
)
)
Hide the two new helper fields, leaving only {Previous Change}
