Running Total

[Rosemary]

How do you make a Virtual Column that contains the running total of values in another column?
My data key is the DATE field. The data column contains Calories Consumed per Day. I want to make a Running Total of Calories Consumed per Day. E.g.: 1800 calories on Day 1, 1700 calories on Day 2, 2000 Calories on Day 3 would give running totals of
Day 1 1800
Day 2 3500
Day 3 5500

Can do it in a heart beat in Excel but can’t find the function on AppSheet. If I do it in Excel and pass it into the app when the data is updated by the app user, the running total formulas in Excel are scrambled. I think this is because AppSheet doesn’t like excel to have different formulas in the same column but by definition a running total formul in excel changes on each row.

Seems like it should be so hard…
thanks
Rosemary

[Marc_Dillon]

https://community.appsheet.com/search?q=running%20total%20%23ask

[Rosemary · Accepted solution]

I found out how to create a Running Total in App Sheet

SUM(SELECT(YOURTABLE [YOUR FIELD],([Date] <= [_THISROW].[Date])))

Cheers
Rosemary

[Rome18]

Hi there, your formula works perfectly on running total. But what if I there is two data on the same day, and I need the total of each row?

Ex:

Day 1: 100; 100

Day 2: 300; 400 (This Value will be 500 if I use that formula)

Day 2: 100; 500

Day 3: 300; 800

Thanks before

[graham_howe]

SUM(
  SELECT(
    yourtable[yourfield],
    ([_RowNumber] <= [_THISROW].[_RowNumber])
  )
)

[Rome18]

This works if the data is recorded simultaneously. What if the 2nd data (Day 2: 300) is inputted the last? I want this so that my client can input the data backdate.

[Mika777]

This is the real difficulty.

[ChrisLW]

Thank you so much for this! I'm a dunce when it comes to using nested expressions, but I'm learning!

[Sara_Bertomeu]

page not found

[Sara_Bertomeu]

page not found

[SkrOYC]

https://www.googlecloudcommunity.com/gc/forums/searchpage/tab/message?advanced=false&allow_punctuati...

[Sara_Bertomeu]

Thanks @SkrOYC

[Mika777]

Please see Rome18's observations.

There is no solution?

[Mika777]

Solved.

Step 1:
In View Options, Sort By:
[Data] (descending)
[_RowNumber] (descending)
Step 2:
In Virtual Column insert below expression:
SUM(
Select(
Moviments[Quantity],
(and(
[Id Product] = [_THISROW].[Id Product],
[Date] <= [_THISROW].[Data]
))))
-
SUM(
Select(
Moviments[Quantity],
(and(
[Id Product] = [_THISROW].[Id Product],
[Date] = [_THISROW].[Date],
[_RowNumber]>[_THISROW].[_RowNumber]
))))

[SkrOYC]

Did you see something like "This expression could affect performance..."?

[Mika777]

Hi, I didn't notice any difference in performance.

[Rome18]

Hi there, yes your formula is correct. But later on if you have big data you can get a performance issue as SkrOYC says. In my App I used static column and Initial value to calculate like you did, and have another action than will trigger if the form is saved, to update all the initial value of thisrow date foward. Hope this helps!