Conditional sum based on data from another table

1

Hi all,

I’m very new to Coda, but I’m loving it so far. I’m trying to figure out how I can insert a sum based on some parameters into another table.

I have two tables:

  1. A table of Worklogs (amount of days), split up by Project and Date
    image

  2. A table of Projects. In this table I would like to add the total amount of worked days from the Worklogs table.
    image

So what I need is:

  1. A sum of all Worklogs for a certain Project
  2. A sum of the Worklogs for a certain Project within a given timeframe.

Does anybody know which formula I should use to achieve this?

Thanks a lot for helping me out, I’m looking to become a Coda expert! :hugs:

-JJ

2

Hi Joachim!

Welcome to Coda :slight_smile:

Try the following and let me know if these work for you:

Total Worked: [All worklogs].Filter(Project = thisRow.Project).[Days worked].Sum()
Total Within Timeframe: [All worklogs].Filter(Project = thisRow.Project AND Date > [Start Date] AND Date < [End Date]).[Days worked].Sum()

3

Hi Chris,

Thanks a lot for helping me out!
I’ve entered the formula’s you have suggested, but the results are zero for all rows.
I’ve added a screenshot to make it a bit more clear. Any idea what I might be doing wrong?

image
image.png1074×1170 81.7 KB

-JJ

4

Hey @jj3000! I’ve been working on this a bit trying to figure it out.

I did get it to work. The odd thing about it is that I have the same formula that you do.

for%20jj2
for jj2.JPG1104×769 56.4 KB

The only thing that I did different to get it to work was I made the all projects project column a select list and made it =[all work logs].project so it created a dropdown for me. That being said I tried it the way that you did it and I got it to work just writing in the text.

Unfortunately that leaves me quite confused as to why it isn’t working!

If you’re open to sharing the doc I’m happy to take a look at it. I can share this one with you and you can take a look at it as well.

Let me know if I can help further! Here is the link: https://coda.io/d/_dzbtq_CLdtv

Let me know if it worked for you - I have google for business so it’s putting the doc behind a wall, which I am going to try and remove now!

feel free to also try this one: https://drive.google.com/file/d/1AXX2oIxTDMWmMweRvYR-fFdIZ-jQBWEK/view?usp=sharing

5

Hi @tom, thanks for helping me out! I didn’t work in my document, but when I copied it in another document to make it available to you, it worked as expected… Smells like a really weird bug to me :wink:

In any case, thanks again for deblocking me!

-JJ

6

I was just working on something with @shishir - trying to figure out how to get lookup() to work. I was way over complicating it and he was kind enough to spend plenty of time with me to figure it out.

That might be something else that you look in to as well. I made a couple of adjustments to that document that I shared with you and provided a couple alternatives to the filter. May work a little more consistently depending on the implementation!

Let me know what you think, but I’m glad to see that it is working for you!

7

I also faced the same problem, on why it isn’t working. I solved it using ToText before the column name. So:

filter(Project=thisRow.Project).... becomes .... filter(**ToText(Project)**=thisRow.Project)

Sorry for bumping up this thread, but thought that other new users (like myself) might find this helpful.

3 Likes
8

I think the problem is that with the original formula we are comparing a list item with a text ( different types of data ). And converting ToText allows you tom compare Text vs Text.