Formula calculation of 2 fields from different rows?

Baserow Help
#1

How can I calculate the difference between two cell contents of different rows or is this not possible?
Ex:
Row 1, field ‘Counter reading’ = 100
Row 2, field ‘Counter reading’ = 175
Row 2, field ‘Difference’ = 175-100

How should the formula for the field ‘Difference’ look like to calculate the difference for the next row from the entered values?

#2

Hello @UweG, if both cells are the same field type (number field type would work the best for your use case), you can easily calculate the difference between two cells’ content using the function ‘minus’, here is the example:

field('Counter reading 2')-field('Counter reading')

image
image2544×512 113 KB

Will this solution work for you?

#3

No, this solves not my problem. Your example has the number fields in one row. I‘d like to calculate the number field of row one with the same number field of the next row ang get an result.
Example:
Number Feld row 1 is 100.
Next row same number field is 175.
Calculation field is 175-100 = 75.
Over next row number field is 215. Calculation is 215-175= 30

#4

Hi @UweG ,

We don’t yet offer an easy way to do this. What you need are LAG and LEAD formula functions which we hope to offer one day but aren’t planned for the near future. The key problem with this functions is:

  1. You create a formula as you describe LEAD('Counter reading')-field('Counter reading')
  2. Now the formula’s cells depend on the order of your rows
  3. What happens if you change the sortings on your table, should all these formula cells be recomputed every time?
  4. What happens in your other grid views, you can have many different views of the same formula cells each with different sorts. We can’t calculate different cell results in the same table per view.

My only idea so far is that the LAG and LEAD functions need to be given a Grid View name as a parameter to use the sorts from that grid view when picking the next/previous row:
lead('Counter reading', 'My Grid View 10') - field('Counter reading'). Alternately perhaps the lead function could take a list of fields to sort by like so: lead('Counter reading', 'Sort Field 1, +Sort Field 2') etc.

Would something like this make sense to you? Do you have any other suggestions on how to implement these functions :)?

One not great workaround

For now you could use the Link to Table field like so to achieve this in a manual way:

  1. Create a Link to Table field, point it at the same table
  2. Link every row to it’s next row
  3. Create a formula which is lookup('Link to next row', 'Counter reading') - field('Counter reading')

But this is very manual and not ideal.