Help needed for building a formula

Hi community,
maybe anyone can help me to build a formula.
I’ll try to describe the problem.
I have a table “Master Service Calendar”, filtered by “Service” for resource planning. On each day an employee gets assigned one or more customers to cover a service (see Screenshot 1 & 2)

I have added 3 columns for the customers “AUDI”, “SPAR”, “MAN” , each with a customer specific formula:
CountIf(thisRow.Customer,ContainsText(thisRow.Customer,“AUDI” ))

If I group this table by month and date, I get a count of assined employees to the specific customer in the summarize row.

What I want to do is creating a table calle “Service Utilization” with the columns “Date”, “AUDI”, "MAN, “SPAR” and write the values from the summarize row to each date and customer. But I don’t know how to build that formula. Maybe it would be possible to write that value in an extra column in the Master Service Calendar table?

Can anyone help me out with a hint?

best regards

Hey @Foerster_Matthias thanks for this super detailed description! Just to make sure I’m understanding correctly, do you want another column that does a sum for each date of those three columns, audi, man, and spar? So for instance in that last screenshot, March 13 has 3 total (audi 1, man 0, spar 3), and you want a column that’d have that total sum of 3 — is that accurate?

Hi Jasmine,

thank you for stepping into this.
Yes, how you understand my description is basically that, what I want. But in the meantime I found a solution.
Because this table is very big with over 44.000 rows (127 employees, each with a pre-filled calendar) I neeeded to move this analyzis job to another table, because it slows down the master calendar.
I have created now a service utilization table for each customer with the following formula:

[Master Service Calendar].filter(Date=thisRow.Date And Containstext(Services,"OS Windows") AND Containstext(Customer,"MAN")).Count()

I got help from outside and as I saw the formula, it was totally clear to me. But without help - I would not know, how to get the result. I am not a programmer and I’m new to Coda.
But it’s really fun to create things in Coda because it is so powerful.

Thanks for your help, Jamine.

Best regards

1 Like

Loveeee this. Thanks for the update and sharing your process with us! :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.