Cumulated expresssions

Romain · 08-28-2019 11:54 AM

Hi,
I have an app that allows the user to enter all the revenue and expenses for his activity.
I would like to make the evolution of his balance according to the data entered.

For this I have these columns [Date] / [In out] (Enum column) / [Amount].
I can calculate his daily balance by the formula below
SUM (SELECT Follow [Amount], AND ([In out] = “In”, [Date] = [_ THISROW]. [Date]))) - SUM (SELECT (Follow [Amount], AND ([In out] = “Out”, [Date] = [THISROW _]. [Date])))

but I can not accumulate it over several days
Day 1 = Balance 1
Day 2 = Balance 2 + Balance 1
Day 3 = Balance 3 + Balance 2

To complicate the calculation, the data are not collected chronologically. On Day 3, the user can enter a day 1 data he had forgotten.

Every day the user can return several incomes or/and outcomes or nothing.

I’m blocking on the formula that would allow me to solve this topic.
Maybe can be done easier in Gsheet but I prefer working on Appsheet formulas if there is a way to do this.
Thanks in advance.

MultiTech

SUM(
SELECT(Follow[Amount], AND(
[In out] = “In”, 
[Date] =< [_ THISROW].[Date]
)))
 - SUM(SELECT(Follow[Amount], AND(
   [In out] = “Out”, 
   [Date] =< [_THISROW].[Date]
   )))

Romain

It is incredible how a symbol “<” make a difference !!!

How I didnt’t think about it, I have tried so many thing and the answer was just to change “=” by “=<”

Thank so much!

Romain

@MultiTech_Visions

MultiTech

You’re welcome!

You wouldn’t beleive how many hours (yes HOURS) have been spent pouring over a huge formula to figure out what’s wrong - only to find there was a single comma missing!

Allan_Souza

Me salvou amigo.