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?
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?
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.
