Can not get the correct data to show in column Gebruik

AppSheet AppSheet Q&A
1

I have the following formulas

2 of them working, but 1 does not

Working
IF(ISBLANK([Beskikbaar_input]),[Ontvang] + IF([_ROWNUMBER] > 1,LOOKUP([_THISROW].[_ROWNUMBER] - 1,“sheet1”,“_ROWNUMBER”,“Beskikbaar”),0),[Beskikbaar_input])

working
IF(ISBLANK([bedrag]) OR ISBLANK([ontvang]), “”, [bedrag] /[ontvang])

Not working
[Beskikbaar] - LOOKUP(MAX(SELECT(sheet1[_ROWNUMBER],([_THISROW].[Beskikbaar_input] = [Beskikbaar_input]))) - 1,“sheet1”,“_ROWNUMBER”,“Beskikbaar”)

When starting the app it already show a total in “gebruik” do not know why but that total is in row above in “ontvang”
I want to show in “gebruik” the valuse of “beskikbaar_input” - "beskikbaar from the row Before,

2

Your previous post, for context. (not sure why you didn’t just reply there)

Try to use code blocks for complex expressions, they’re incredibly hard to read as you’ve written them.

Also, mention where you’re using these expressions. Are they in specific columns as app formula or initial values, or what?

Your first expression:

IF(
  ISBLANK([Beskikbaar_input]),
  [Ontvang] + 
    IF(
      [_ROWNUMBER] > 1,
      LOOKUP([_THISROW].[_ROWNUMBER] - 1,"sheet1","_ROWNUMBER","Beskikbaar"),
      0),
  [Beskikbaar_input]
)

This is not the correct usage of the OR() function.

https://help.appsheet.com/en/articles/2357288-or

Your 3rd, “non working” expression:

[Beskikbaar] - 
  LOOKUP(
    MAX(SELECT(
      sheet1[_ROWNUMBER],
      ([_THISROW].[Beskikbaar_input] = [Beskikbaar_input]))) - 1 , 
    "sheet1","_ROWNUMBER","Beskikbaar")

Since you’ve disregarded my warning from the previous post, and are moving ahead with your initial plan, where you’re heavily relying on rownumber for all of your functionality…

I’d recommend using that _rownumber as your table’s key column. Then add a new column into your sheet which is a reference to the previous row, with a simple expression of [_RowNumber] - 1.
You can then dereference off of this column very easily, instead of using lookup() or max(select()) or whatever. So it’d just be something like this to calculate your new [beskibaar] amount:
[previous_record].[Beskibaar] - [use] + [receive]

https://help.appsheet.com/en/articles/961426-references-between-tables

https://help.appsheet.com/en/articles/1090811-dereference-expressions