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

2 Likes

Hello, @Krunal_Sheth and thanks for the solution.
I can’t fully test it yet, but hope this will work for me as well.

Could you look and this screenshot of my table and help me?
I don’t know why I have an output “1905” instead of the year"2019, 2020 and etc.

image

Hi,

Welcome to the community! I hope that you will enjoy Coda.

The column in which you have your dates - what is the column type? And the display option setting?

It is very helpful if you can share the doc, and if possible give access to it or a safe copy as per below:

Regards
Rambling Pete

1 Like

Good day!

Here it is - Sales

Hi

I have changed the column type of Year to text, and it now displays the year as expected.

The column type was originally set to date column. The formula result (e.g. in line 1) in the number of 2021, and which the system then translates into a date. The number 2021 is equivalent to date 13 July 1905.

Regards
Piet

2 Likes

Thank you for your help!

1 Like