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
Solved! Go to Solution.
I found out how to create a Running Total in App Sheet
SUM(SELECT(YOURTABLE [YOUR FIELD],([Date] <= [_THISROW].[Date])))
Cheers
Rosemary
I found out how to create a Running Total in App Sheet
SUM(SELECT(YOURTABLE [YOUR FIELD],([Date] <= [_THISROW].[Date])))
Cheers
Rosemary
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
SUM(
SELECT(
yourtable[yourfield],
([_RowNumber] <= [_THISROW].[_RowNumber])
)
)
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.
This is the real difficulty.
Thank you so much for this! I'm a dunce when it comes to using nested expressions, but I'm learning!
page not found
page not found
Thanks @SkrOYC
Please see Rome18's observations.
There is no solution?
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]
))))
Did you see something like "This expression could affect performance..."?
Hi, I didn't notice any difference in performance.
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!
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |