The Sum for Every Unique Date

1

Hi there, I’m been enjoying using Coda lately although I’m still finding it challenging to create views or tables that list specific results.

I have a very simple example with a list of projects where each record is a date and time spent on the project. As you’ll see it’s possible to have multiple records for the same project on the same day. What I’d like is have table list the total time spent per date. This would allow me to record over time the overall amount spent on each unique date.

Here’s a small example of this.

image
image.png700×791 17.3 KB

Ideally the Time Spent by Date table would list 3 unique records in this case

1/31/2018 -> 8
4/30/2018 -> 4
5/17/2018 -> 6

How can I have the Time Spent by Date automatically track new dates when they are added to the main Log table and the total amount of time spent for that date?

Thanks in advance for your time.

2

Dear @anon45019700,

Hopeful the doc support you enough to understand how it works. ( 2 options)

This i the formula:
[Team Task Tracker].Filter(Project= thisRow.ProjectID).[Time spend].Sum()

Kind regards,
//JP

3

Hi @Jean_Pierre_Traets thank you for sharing this example with me. You reminded me that I could leverage groupBy in part of my use case. I understand your solution and it helped solve my problem with one remaining question.

I like how you created the SumTimeSprent table and formatted the ProjectID column as a select list. That works great but this step is manual. In any case, if a new project is created, I would need to manually add that project to get it’s sum time spent calculated. Is there a more automatic way of doing this to eliminate the manual step?

In my example what I’m trying to track are dates instead of projects and more specifically unique dates. And from those dates I want to be able to calculate the total sum occurring in each of those dates.

4

Hi,

For your scenario buttons that create new rows (like for projects) in the appropriate tables solves the problem of missing content.

Filters with AND / OR will create the opportunity to get the data delivered as of your needs.

Feel free to play around and review material shared in this community.
Where possible, other members and we will be glad giving a helping hand.

Kind regards,
//JP

2 Likes
5

I hadn’t thought of buttons but I suppose they are a step closer to being fully automated. I will play around with that. You’ve definitely unstuck me, thank you.

2 Likes
6

Hi,

I have just amended the grouping by date and time spend, to be more in line with the kind of result you expected.

Have a good day,
//JP

7

In case it is helpful to others, I had essentially the same question that was asked at the top of this thread, but for a canvas formula instead of a derived grid. Essentially, my question is how to generate the sum for each unique date, but as a list of sums.

First, generate the set of unique dates:

= Logs.Date.Unique()

We can run these through a formula map to generate sums for each day:

= FormulaMap(Logs.Date.Unique(), Logs.Filter(Date = CurrentValue).[Time Spent].Sum())

This generates the output 8 4 6. If we want to get really fancy, we can generate Miguel’s original textual output in a bulleted list as follows:

= FormulaMap(Logs.Date.Unique(), Concatenate(CurrentValue, ' -> ', Logs.Filter(Date = CurrentValue).[Time Spent].Sum())).BulletedList()

Which yields:

  • 1/31/2018 -> 8
  • 4/30/2018 -> 4
  • 5/17/2018 -> 6
3 Likes
8

Dear @Mike_Hewitt,

Thanks for your input, as you mentioned this actually replies to the title of this post and is an realistic use case.

Always eager to learn :innocent: