Example: Community #8292 - Grist
You have an Inventory table. Every time you receive a delivery of a SKU, you enter it into this table. When you enter a record, you would like to know the cumulative Stock In for that SKU.
We use the PREVIOUS() function to do the cumulative calculation.
The formula in the Cumulative Stock In column is;
previous_stock = PREVIOUS(rec, group_by=("SKU"), order_by=("Date")).Cumulative_Stock_In
previous_stock + $Stock_In
group_by allows you to group by some column value. In this example, we are grouping by SKU. Only records with the same SKU should be considered in the calculation.
order_by allows you to specify in what order records should be organized when doing the calculation. In this example, it makes sense to order by Date so cumulative total follows with the order in which orders are received.
The PREVIOUS() function returns the previous record found based on the group_by and order_by parameters. We use dot notation to specify which column value we want returned for that record. More often than not, you’ll want the value in the column where you are entering the formula. In this example, the column where we are entering the formula is Cumulative Stock In.
In the second line of the formula, we want to add our previous cumulative value to our Stock In value to find our updated Cumulative Stock In value.
Now we can see a running total of the stock received for a SKU.
COMMON ERROR
TypeError : unsupported operand type(s) for +: 'NoneType' and 'float'
If you see this error, update the column type to NUMERIC for any columns used in the cumulative calculation. We are doing a mathematical calculation in the second line of the formula so it is expecting numeric values.
Be sure to check out our other Cumulative functions, NEXT() and RANK()!