Help

Running Total

Topic Labels: Extensions Formulas
Solved
Jump to Solution
1190 4
cancel
Showing results for 
Search instead for 
Did you mean: 
triple
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello Airtable Community, 

I hope all is well! I am having trouble with creating a running total formula/script. In the first table called "General Monthly KPIs" there is a field linking to the "Transactions" table with the dates, month and year, and the amounts of each transaction. There is a rollup field in the General Monthly KPIs table that calculates the monthly burn of each month. How can I create a running total field? There are thousands of transactions, so the 1-to-1 and 1-to-many method will not be applicable. 

Screenshot 2023-08-07 at 5.51.14 PM.png

Screenshot 2023-08-07 at 5.51.21 PM.png

I tried scripting, here is my script:

Screenshot 2023-08-07 at 5.57.44 PM.png

 Which resulted in an incorrect running total:

Screenshot 2023-08-07 at 5.58.18 PM.png

Does anyone know how to create a running total?

I hope you all have a great day!

- Ella

2 Solutions

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hi @triple,

"Month and year" is a string, so if you sort it in ascending order, it will be in ABC order.
You need to sort by date value, or a number such as 20230808

See Solution in Thread

Ron_Williams
6 - Interface Innovator
6 - Interface Innovator

Changing the date field to a date field instead of a text field would should fix your script. Alternatively, you could use an auto number field and sort with that instead. Just make sure when you create the field the sort is in the order you want. 

See Solution in Thread

4 Replies 4
Sho
11 - Venus
11 - Venus

Hi @triple,

"Month and year" is a string, so if you sort it in ascending order, it will be in ABC order.
You need to sort by date value, or a number such as 20230808

Ron_Williams
6 - Interface Innovator
6 - Interface Innovator

Changing the date field to a date field instead of a text field would should fix your script. Alternatively, you could use an auto number field and sort with that instead. Just make sure when you create the field the sort is in the order you want. 

triple
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you so much! It worked perfectly.

Thank you!! I added a field called "month last day" and replaced it in the script-- worked perfectly!