How to lookup and sum values of another column?

[yscias]

Hi, i’m trying to create a table to forecast and organise my payables according to due date. But i seem not to be able to lookup or sum
up the right amounts. How do i go about it? I’ve wrecked my mind for hours about this looking through articles and videos, but couldnt find the answer. What am i doing wrong?

[Pch]

Hi @yscias and Welcome to the Community !

I think there might be 2 reasons for why it’s not working as expected …

1. I think the [Due Date] should be less than thisRow.[End Date]

   In other words, your range should be :

   thisRow.[Start Date] < [Due Date] < thisRow.[End Date]

   But here you have (at least, from the screenshot your shared … Thanks for sharing ) :

   thisRow.[Start Date] < [Due Date] > thisRow.[End Date]

2. The range you created within your Filter() is strictly exclusive so it won’t return the amounts for the cases where the [Due Date] is equal to your [Start Date] and your [End Date]

   E.g.: If your [Start Date] is 01/09/2022 and your [End Date] is 10/09/2022, your Filter() will only returns the amounts for the [Due Date] going from 02/09/2022 to 09/09/2022.

   If this range is a personal choice, that’s totally fine …
   But if you wish to make your range inclusive, to include both the [Start Date] and the [End Date] and have the filter returning the amounts for these dates too, you would just need to use >= (greater or equal) and <= (less or equal) instead of > (greater than) and < (less than)

So, with all this, I guess the formula you’re looking for would be something like :

Payables.Filter(
   thisRow.[Start Date] <= [Due Date] AND [Due Date] <= thisRow.[End Date]
  ).Amount.Sum()

(Note that [Due Date] is in fact CurrentValue.[Due Date])

I hope this helps

[yscias]

Thanks a lot for your help! I have corrected the mistakes i made, but the filtering/formula is still not working as it should Does the database need some time to update with new info or something like that?

[yscias]

Thanks for letting me know about the operators too! I tried =< and => previously which gave me an error, so i just used the < or > operators instead. Do you know where can i find a list of operators that are used? I tried searching through the formula page on coda website but i cant find it.

[Pch]

@yscias

I have corrected the mistakes i made, but the filtering/formula is still not working as it should

Ah! I might have another idea … As I think I misunderstood your request a little (sorry )

The formula I gave you in my previous reply would return and sum all the amounts for the dates within the ranges you set up in your Planner table without taking into account if the amount was paid or not …

So I guess, this might be where the issue is …

If you need to only sum the paid amounts from your Payables table, the expression

thisRow.[Start Date] <= [Due Date] AND [Due Date] <= thisRow.[End Date]

… will not be enough in your Filter()… You would also need to tell Filter() to look for rows in your Payables table where the Paid checkbox is checked (i.e.: true)

So the formula would be something like this:

Payables.Filter(
  Paid                        
  AND thisRow.[Start Date] <= [Due Date] 
  AND [Due Date] <= thisRow.[End Date]
).Amount.Sum()

This Filter() formula will go through your Payables table and return the rows where the checkbox Paid is checked (a checkbox being a boolean and having only 2 viable states (true or false), you don’t need to precise the = true here, it’s implied) and the Due Date is within the range … All that’s left is to access the amounts of these rows and sum them

Now, if you need the opposite …

Payables.Filter(
  Paid.Not() 
  AND thisRow.[Start Date] <= [Due Date] 
  AND [Due Date] <= thisRow.[End Date]
).Amount.Sum()

It will do same thing as the previous one when it comes to the range but it will only return (and sum) the amounts where the checkbox Paid in your Payables table is Paid.Not() (in other words, Not() true, so false )

I’ve created a simplified version of your doc based on your screenshots so you could see the 3 different Filter() formulas side by side …

The first one in the[All Amount in range] is the sum of all the amounts within the date range regardless of the state of the Paid checkbox in the Payables table.
The second one, is the Paid only (within the date range) and the last one is the not Paid only (still within the date range)

@yscias

Do you know where can i find a list of operators that are used?

I do not know that … But I can give you the list though …

• = equal
  • As in a = b: a is equal to b
• != not equal
  • As in a != b: a is not equal to b
• > (strictly) greater than
  • As in a > b: a is strictly greater than b
• < (strictly) less than
  • As in a < b: a is strictly less than b
• >= greater than or equal
  • As in a >= b: a is greater than or equal to b
• <= less than or equal
  • As in a <= b: a is less than or equal to b

The magical auto-suggester of Coda should help you with that too when you write certain formulas where you would need one of those …
(The auto-suggester won’t necessarily be triggered in that fashion with dates as the auto-suggester tends to suggest anything related to dates in this case )

I hope this helps