In general, Quick Base records don't know about data on other records without a relationship in place.
The simplest setup I can think of to accomplish what you outlined here is to create a relationship from your Units table to itself. You could then have a summary field in the Process to Unit relationship to show what the latest Record ID# is, setup that in a lookup field back to the Units table, a snapshot to capture what that value is as a new Unit is added, and an Automation to populate the reference field in the relationship I proposed to link them together.
With that relationship in place, you can add the Actual Units and Forecasted Units fields as lookup fields so that you can easily see what the previous month's metrics were. Your current formula could then be based on those details like this:
If([Previous Unit - Actual Units]=0,[Previous Unit - Forecasted Units],[Previous Unit - Actual Units])*[Related Process - Growth Rate]
------------------------------
Eric Mohlman
------------------------------
Original Message:
Sent: 11-05-2019 10:49
From: Noreen Justice
Subject: Calculate a Forecast Based on Previous Month Numbers
Update: ON HOLD FOR NOW
I have records that track production volumes. The records include 2 values: an actual amount and a forecasted amount. I would like the forecasted amount to be a formula field but I'm struggling with how to write the formula. The formula should be based on a set growth rate that varies by the Business Dept. Here are the fields:
Record ID | number |
Related Business Dept | Relationship look up populated from Process ID |
Related Process ID | Relationship populated ID |
Period (always 1st of the month) | User entered date |
Actual Units | User entered number |
Forecasted Units | number formula to calculate forecasted amount + growth rate. Each Business Department will have their own set growth rate. The example here uses 0.02 |
| If Business Dept = xxx and previous month's (same Process ID record) Actual Units is 0, populate Forecasted Units with (previous month's Forecasted Units + (previous month's Forecasted Units * 0.02) |
| If Business Dept = xxx and previous month's (same Process ID record) Actual Units is >0, populate Forecasted Units with (previous month's Actual Units + (previous month's Actual Units * 0.02) |
Sample
Record ID – 1
Business Dept – ABC
Related Process – 228
Period – 5/1/19
Actual Units – 75
Forecasted Units – 50
Record ID – 2
Business Dept – ABC
Related Process – 228
Period – 6/1/19
Actual – 0
Forecasted Units – 76.5 Use previous month's Actual: 75 + (75*0.02)
Record ID – 3
Business Dept – ABC
Related Process – 228
Period – 7/1/19
Actual Units – 0
Forecasted Units – 78.03 Use previous month's Forecast: 76.5 + (76.5*0.02)
Any help would be greatly appreciated!
------------------------------
Noreen Justice
------------------------------