Sum of values limited to "From Date" to Date

[Andre_Zijlstra]

While organizing an event I have this simple table:
https://airtable.com/shrClDgt5JN17c7XX

People register on a certain date, and pay a certain amount
The same people pay what they are due a week or 2 later.

Now, here’s my thing: I want to know per day how much money is due. So if 2 people register on day 1, at the end of day 1 $30 is due
On day 2 $45 is added,
On day 2 a total amount of #30+$45=$75 is due.
On day 7 people start paying.

What system could I use to visualize per day how much money is due? In a spreadsheet you can make use of sumif / sumifs but that is not possible in AirTable.

[Andy_Lin1]

I believe you would need a Pro plan feature (Blocks) to get a visual representation.
However, you can get the daily sum by simply grouping by the {Date Action} field in your table.

[Julian_Kirkness]

I’m assuming here that you aren’t looking to be able to see a running total by day:

Day1: 30
Day2: 75
etc

As a ‘report’?

---

If you need the total due to date - the sum of the figures due - sum of payment received, then there are a couple of ways you could do it…

1. You could add a payment received field to the record and then you could subtract the payment received from the amount due to give you a balance and then the total outstanding is the sum of the balance field.

2. You could add a Transaction type field to the record (booking or payment) and then use hold use a formula to calculate a field to the due value or the payment value * -1 IF(Type = “Booking”, Amount, Amount * -1)

3. You could simply record the Payments as negative values - so the sum would be the amount outstanding. If you’re on a Pro plan, you could then use record colouring to show which values were payments and which bookings based on the value - or use a formula field if not.

You could discover the outstanding amount on any day simply by using a filter on the table.

Hope this helps.

[Andre_Zijlstra]

Calculating the difference between the amount Due and Paid per date is not the challenge.
I can group the date, and it will show me the balance per date. But that’s not what I’m looking for. I want to know the running balance, per date.

This picture shows the seperate bookings, with the Due column and the Paid column. Adding a column would show the balance per booking {DUE}-{PAID}, and when grouped in a Date, it shows the balance per date.

But I need this:

The balance per date is shown (AirTable would show that when the Date is groupled), but it wouldn’t show me the running balance,

AirTable would show the running balance below on the page, but that is not enough! I really need to see the running balance per day.

So how do you calculate that?

[Julian_Kirkness]

Thanks for the clarification @Andre_Zijlstra - I think this may be a case of trying to fit a square peg into a round hole!

Spreadsheets are good at this type of thing because the Location of the data points relative to each other is relevant (i.e. previous row has some meaning). However, Airtable is a database and this doesn’t follow.

You are therefore probably better off using a spreadsheet for this particular calculation. If you want to manage your data in Airtable you could use a simple Zap to copy each new record into a new row in Google Sheets - and then apply the formula. If you do this remember that it won’t take care of record deletions.

[Andy_Lin1]

The really dirty method of doing this in Airtable would be to create a separate table of dates, which I’m pretty sure is nobody’s idea of a good time, because everything has to be linked manually.

[Andre_Zijlstra]

Thanks guys, I kinda had the impression a solution would have to be found in workarounds, if at all possible.
I would be nice to have the possibility to link to something like “Previous Record”.

[Pobor]

Exactly as outlined by Andre above I also need a running balance in a table.

Beeing able to show a running balance is a basic and necessary funcion when programming administrative systems.

Just sum present record with all previous records. This funcion would be greatly appreciated.

Taking about fitting a square peg into a round hole is not serious. This kind of arguments are often used by softwere developers lacking necessary skills to solve simple every day issuse.

Big fan of Air Table, but it can always be better.

//Brp

[kuovonne]

Welcome to the Airtable community!

This thread is very old and Airtable has many features now that did not exist five years ago.

It is now possible to calculate a running total using a script.