Total back the most recent 7 days (entries)

1

Hi everyone, I’m looking for a little help. How can I write a formula to go back the last 7 entries for a column and total them? Example, how many apples did I eat in the last seven entries (days)? And as I add on to the column, it will keep totalling just the most recent 7. I want to use this to calculate how many hours an employee has worked in the last 7 days and keep it rolling forward. Hopefully, this makes sense to you. Thanks

2

@Chris_Harris

See if this thread helps: How do I show the last 7 days in a formula?

1 Like
3

Awesome, Thank You!!!

1 Like
4

The above didn’t quite work as I need. Here is a quick Loom (video) show how the formula works in Google Sheets. Can I get Coda to do the same?

Thanks for any suggestions.

Chris

5

@Chris_Harris

Take a look at this doc that contains two different approaches, each one illustrated on its own section.

6

Thank You Ander, This looks like it will workf or me. I will work away with it.

Thank you, you are making a difference.

7

Ander, I’m sorry. Again, not working. If you would allow me to give a little more detail. I’m in the trucking business. I perform audits on trucking companies. One of the laws is that a truck driver can only work 70 hours in any 7 day period (and sometimes 120 hours in a 14 day period).

When I do an audit, I look at the drivers history for up to a 6 month period. So I would normally enter the driver hours onto a spread sheet and have it add up the hours. In a 30 day month, there should be 23, 7 day periods, as truck drivers can work 7 days per week.

In a month the first 7 day total will happen on calendar day 7. Then another 7 day total on calendar day 8 of the month (I drop the hours worked on the 1st of the month and SUM days 2 to 8) and then on calendar day 9 another completion of the most recent 7 days (SUM days 3 to 9). And on it goes for the complete 6 months of the audit. I need to be able to flag when a driver violates the allowable hours. (And of course I’m doing multiple drivers)

This is what I’m trying to do. I can do it in a spreadsheet. But with Coda, I’m doing so many other things that a spreadsheet doesn’t do well. I have managed to move or create all the other functions of the audit in CODA. This is the last step (I think).

I’m hoping that you or someone in the community can help.

Thanks again. you have been very helpful.

8

@Chris_Harris

I added a third section to that doc (see below).

First, let’s determine how you want to calculate the rolling 7-day sums, and then we can determine how best to handle the dimension of multiple drivers.

In a spreadsheet, you calculate rolling sums by targeting the coordinates (columns & rows) that coincide with a rolling 7-day block. In Coda (relational data tables), you do this by defining the list of rows that will comprise your rolling 7-day block, and then summing values from those 7 rows.

It’s a different way of thinking. Your brain must shift gears.

Here’s the formula from the column called [driver A rolling 7 day sum]:

If(
  thisRow.[visual position of row]<7,
  "",
  thisTable.Filter(
    date<=thisRow.date AND
    date>=thisRow.date-Duration(6)
  )
  .[driver A daily hours].Sum()
)

That Filter() formula returns a list of 7 rows relative to the position of the current row (called thisRow).

Here’s where you can read documentation on the various formulas: https://coda.io/formulas

Study the formulas in the demo doc until you understand exactly what they are doing. Let me know if you have questions.

9

Thanks Ander for all that you have done. I will study the formula’s as you suggest.

1 Like
10

Ander, I could use your help again. It is in regards to sort and sum’ing by driver. I have tried, but with no luck. Could you suggest a way to sort the table by driver and sum the previous 7 days? Or do you suggest that I create separate table for each driver being audited?

Happy long weekend.
Chris

11

@Chris_Harris

Can you share a recent version of your doc (or a dummy doc) so we can see where you’re at with this?

  1. How many drivers would someone be auditing during a given time period?
  2. How many drivers would they be auditing over the lifetime of a doc?
  3. Are these recurring audits of the same drivers over regular intervals?
  4. Or one-off audits of random drivers at irregular intervals?
12

Ander was kind enough to help me come to a solution for this off line. This has been resolved. Thanks ANDER

1 Like