Grouping Data by Time Range

Hi all, I’ve been using Coda for various time series metrics around various things like financial data, etc. Up until now, I’ve been doing graphs based on the date or timestamp for each metric, but I’m running into a wall where I want to aggregate metrics by week, month etc.

The workaround I have right now is not very elegant. I have Period tables, that have a Start Date, End Date, name and a length. For example, “January 2019”, 01/01/19, 01/31/19, Monthly.

From that, I have a summaries table that has an entry for each dimension + period above that I want. If I have a Grocery account I want to group by month, I have an entry with the Account, the Period, and a Balance column which automatically computes based on the Account and Period columns. I can then graph rows from that Summary table.

As you can tell, the annoying thing is to have to automatically generate the Time Periods rows and then Summary rows on top of that. At this time, the only way I can figure out how to do that is through the API from my private code.

Any thoughts on how to improve this, and/or make it more automatic without requiring writing code?

hi @wisosim,

I am not entirely following the problem? you could use DateTimeTruncate(thisRow.Date,"Month") to come up with grouping on the month

here’s an attempt