Hi there,
I’m currently using a formula to sum a number grouped by people—which works great!
The formula I can’t seem to figure out now in the table filter is to show unique rows based on a people column.
Context: I track the number of email replies our team sends in a table, per day. I’m filtering based on the last 7 days, and I would like to know the sum of replies sent in those 7 days, and only see one row per team member.
Right now, the sum is working, but I see the same team member multiple times (as many rows there are for the days replies were sent):
This likely needs something like: thisTable.Filter(People=thisRow.People).Unique() but this is incomplete/incorrect.
Thanks for the assist!
Hi @Leo_Selie1,
The WeekNumber formula would enable you to group all the records for a particular week.
I hope that helps, but if you need further assistance, could you share your doc at all?
Rohan
Hi Rohan, thanks for helping out!
For PII-reasons, I can’t share the doc, but the goal here is to filter out duplicate rows based on the unique people from whatever is in the table.
The week number formula is working as intended, I just need to filter out rows so I can see only unque people.
@Leo_Selie1 I put together a quick demo of what I think you’re trying to do.
Basically, I created a new view of the original table with a summary.
Let me know if you have any questions!
Hi @Troy_Larson —thanks for the assist, and sharing the demo!
This is indeed what I created earlier, but the problem is that I can’t sort on the sum. So, basically what I did is use a formula to create a sum, aggregating per “people”. This does work really well, however, I just need the unique rows based on the people, otherwise I can see the top-scorers in the table multiple times.
@Leo_Selie1 If you look at the demo again, I think this is what you are trying to do.
The top table is a list of email replies per day per person.
The bottom table is a view of that table. I just added a summary total on the total replies column, grouped by Person.
When you collapse the column, it gives you only one person per line, with their total replies.
Hi @Troy_Larson , thanks so much for the continued effort!
This is indeed also another solution I had tried, and while it does work for the sum and grouping, I can’t sort on the SUM itself in descending. I can only do that, I believe, if there is a separate column in the table with a sum per person and therefore I’m getting the near duplicate records.
The idea is to create a weekly leaderboard and show the top senders at the top of the table.
I wonder if, perhaps, this is just not possible right now?
Got it – I love the Leaderboard use case – I’ll update the demo today!
Hi @Troy_Larson, were you able to find a solution for creating a leaderboard, and filtering out the duplicates?
Thanks!
Yes! (It’s in the original link I posted)
It took a BIT of trickery, but not too crazy.
- Each row has a SUM for the week based on the
Week column. The same person may have multiple days in a week.
- The
Display in Leaderboard column is used to to ensure that only ONE record per person per week is selected.
- The
Weekly Rank is a bonus column I created so you have that as well.
- The leaderboard simply shows the rows where
Display in Leaderboard is selected, filtered by week.
1 Like
Hi @Troy_Larson , thank you so much! This is indeed EXACTLY what I was looking for (the Display in Leaderboard field formula)—thank you so much!
Although I wouldn’t need it now, I’d like to add on a dimension if possible?
So, we have different people spending different time on email. E.g. person A could do 10h in a given week, and person B could do 20h.
What I would love to calculate is the replies per hour, calculated on the week:
E.g. (sum of weekly replies) / (sum of weekly hours)
Important here is that we shouldn’t do the average of an average, rather calculate RPH based on the whole week, so each day isn’t weighted equally per day, but rather calculated on weekly sums.
Then do a similar approach to rank highest RPH on top and have one record/row per person, per team, per week.
Would that be possible?
Yes! Take a look. I just used a similar approach to add rank column for RPH . . .
