Help

Re: How to get summaries (rollups) for Timeframes

883 0
cancel
Showing results for 
Search instead for 
Did you mean: 
4N
6 - Interface Innovator
6 - Interface Innovator

Hi everyone, 

I build an internal tool for a company where they organise their Projects, Invoices, Employees etc. 

As the Data is accumulating, we would like to get a better overview,, especially in the sense of knowing how the numbers look on a monthly basis. 
However this seems to be trickier than expected. For example Invoices: I want a summary for each month. Sum ingoing, sum outgoing, balance.
Or I have the hours worked by each employee. I'd like a table where I have those numbers for every employeee on a monthly basis. 

What would be a good way to collect all sorts of data based on predefined (month, year etc) timeframes? 

I dont want to work with a custom view for each month. I tried displaying the dates as "March 2024, April 2024" but i cannot group by that. I thought about creating a "timeframe" table where every record is one month for example, but then I'd have to link every record (from different tables) that falls into that timeframe. 

So most complete solutions seems to be the latter but that would need a bunch of automations or scripts and can probably break easily. 

Is there any other smart way of going about this that I'm missing? 
Any help would be greatly appreciated!


3 Replies 3

I normally end up with that "Timeframes" table too, with an automation that'll paste the contents of a formula field that outputs that "March 2024" etc into a linked field.  Works alright, but would be curious about other options too!

Here is a sample base I created to show how to aggregate data over time.  This creates a link in every record to an Aggregator linked record. From there I use rollups to count want I want to measure. Then on a daily basis, I copy those aggregator values into the historScreenshot 2024-03-26 at 4.38.34 PM.png

 

ical log file.

Andrew_Percy
5 - Automation Enthusiast
5 - Automation Enthusiast

The new pivot tables in interfaces should give you this. In various bases we take the primary date field and then create subfields with formulas for some combo of year-month, and then we are able to use those fields  in selecting and grouping.