Hi,
after hours of try and error I decided to ask the community.
At the moment I create a sheduling doc, which contains Dashboards, planning and reporting areas for different teams within the same department. The reporting shall be even on individual level at the end.
Basically I have a core table, which contains all shifts ever planned and for reporting I want to know, who was planned how long for each tasks in defined time frames (like: person A did 60 hours phone calls and 40 hours e-mails with the last 3 weeks).
I want to have the individual results aggregated within 1 single, extra table.
My basic table looks like this:
Name +++ Date +++ Tasks +++ 1 Tasks 2 +++ Task 3
(Person A +++ today +++ Phone +++ Mail +++ Chat)
(Person B +++ today +++ Phone +++ Phone +++ Phone)
(Person A +++ yesterday +++ Mail +++ Mail +++ Mail)
The extra table shall look like this:
Name +++ Phone (hrs assigned) +++ Mail (hrs assigned) etc.
(Person A +++ 60 hrs +++ 40 hrs
(Person B +++ 40 hrs +++ 0 hrs)
I tried “lookup”, “filter”, “contains”, sum, sumif etc… But I can’t find a way. I could create an extra view, filtering for a specific person, but that’s too much work for 100+ people.
Is there any way to “filter”/aggregate the data within a formula?
Hi,
to give more information:
[Report Raw Data All Agents] = table was all single shifts being entered
[Reports: Core Times (allocated hours per task per agent)] = table, where each person is mentioned once only. Here I want to see the aggregated results for each individual.
Basically I wanted to try to have a match in the formular like:
If(thisRow-Name=[Report Raw Data All Agents].Name,SUM([Report Raw Data All Agents].[Count Phone]),0)
The result is always 0.
I also thought about this:
[Report Raw Data All Agents].[9:00].CountIf((CurrentValue=“Phone”))
But also here I did not figure out how I filter the data for single persons.
Does anyone have an idea?
I think you’re on the right track with filtering.
Here’s a simplistic example:
This just filters based on names, but you could expand this to handle the activity type too.
Right now the formula is SUM(Activity.Filter(Name=SummaryName).Amount)
If you imagine also adding a column to the Activity table called Type, with Select dropdowns for Phone vs Mail, and then adding a column to the Summary table called SummaryType, your formula would instead be:
SUM(Activity.Filter(Name=SummaryName && Type=SummaryType).Amount)
Note: It’s not necessary to have the “name” column in the summary table be called something different (both tables can have columns called “name”), but it’s easier to understand the formula this way 
2 Likes
@Nick_HE
Thanks for your replay.
I tried to rebuild it:
For some reason it says the result is always 0. When I replace “Name (Overview)” by a specific name it works perfectly. Any idea, why?
Can it be a problem, that one “name” column is formatted as “lookup from table”?
Yep I think so - one column is just text, and the other is a living breathing connection to the record in the other table, so they’re not strictly speaking “equal.”
You can either:
- make the columns in both tables be formatted as lookup from table, or
- for the one that’s a lookup, add a dot reference. so instead of saying
Filter(Name= you would go Filter(Name.blabla= where “blabla” is a column reference within your people table that you’re looking up in (so maybe something like Name.[First Name] or whatever you called the column in your people table.
Ah thanks god. Finally it works!
