Summarising running data for different periods

This is very basic but I am not sure the best way to do it.

I am adding a running log to an app. This will include data like date / distance / time and then calculations eg pace. There may be more than one entry for one day. I can put this in one data sheet.

But I also want running totals eg last 7 days total distance / last 4 weeks total distance / calendar month total distance / calendar year total distance.

What is the best way to do this? Do I need to have these calculations in a separate sheet (and how)?

Also I would like to be able to give option of displaying the total for a selected Month / Year etc.

Can anyone suggest the best way to do this?

Thanks

Paul Winterbottom

This is a fairly common thing, and there are a number of ways to approach it.

What I like generally like to do is something like:

  • calculate the date (or date range) that I’m interested in
  • apply that to every row in my data table
  • use an if-then-else column to compare the date of each data row to my calculated date (or dates)
  • summarise the results of my if-then-else column

So to take your example of the Past 7 Days, what you could do is:

  • Add a Math column to your Log table using the formula Now-7. This will give you the date of 7 days ago. (Note that Now is the special value that gives the current time - you’ll see it as a replacement option in the math column)
  • Create an if-then-else column:
    – If date is on or after the math column, then distance
    – Now use a rollup column to sum the values in your if-then-else column to get the total distance in the past 7 days
    The same can be applied for the past 4 weeks (28 days) or any number of days.

When it comes to calendar month, it’s a little tricker. If you’re talking about the current calendar month, then what I would do is create two math columns as follows:

Year(Now) * 100 + Month(Now)
Year(Date) * 100 + Month(Date)
  • In the first one, substitute the current datetime, and in the second one substitute your Log datetime.
  • This will give you 2 numbers that you can do a direct numerical comparision on:
    – If mathNow is equal to mathDate, then distance
  • And once again, use a rollup to calculate the total

There are other approaches that use relations and helper tables, etc. There is no single best way to do it. It really depends on your desired user experience - ie how you want to present the data, and allow the user to interact with it.

2 Likes

Thanks very much. It’s very helpful. Sorry for not replying sooner but I have been away for a couple of days. I will try your suggestion next week.

Paul

I have tried this and it works perfectly! Thanks very much for your help.

Paul