Skip to main content

Hi,

We’re trying to analyse operational and financial data longitudinally, over time, where e.g. weekly sales averages would be shown in a table in an interface.

I know I can have averages with Numbers in a dashboard, but it’s not possible to calculate averages in formulas (like in Excel, one can sum vertically across a certain field). This would e.g. allow us a rolling 6 month sales average.

Another need is to be able to create projections, based on historical data. For this one would need the earlier mentioned longitudinal averages, and then have some coefficients that one can change and make scenarios. Any ideas how to achieve that?

Multiple columns each consolidating data for a certain week? That becomes difficult since neither weeks or month can be defined as “after/before” a certain number, only the previous and the next are available directly.

rgds,

Björn

Could you provide some examples of what this would look like in Excel?  

For the weekly sales averages thing, I handled this before with a table where each record represented a single week and linking the data records appropriately if that helps

Ideally you can share screenshots of your excel setup.

What data structure you have, and what your numbers and charts look like!

Maybe you can even share access to a dummy google sheets you have?

Thanks for replying ​@TheTimeSavingCo. I actually figured this one out myself, at least partly, but haven’t gotten it final so I could post back here. I think this is more or less what you did also?

I created another table “Finance monthly” with one record per month. Using one field for start of month with manual input (drag-copying down is efficient to fill a year or two ahead), and another with a formula for end of month, and the primary field formula based on these two gave me YYYY-M.

I then linked all projects to these records into their respective record (month). Based I can make calculations that I link back to the main “projects” table.

Not ready with projections, need to get back to that.

Cheers,

Björn

Usually, when you need such type of summary, you should create formula field in your basic table (like you did YYYY-M). Then create linked field to a new empty table and copy-paste whole formula column into that linked field. Such bulk-linking works like:
 find this value in a primary field of linked table and link to it. if not found, create such record and link.
 thus, you will get the unique list of YYYY-M in other table.
The only thing to care about is - when you have new record, and fill date so the YYYY-M has its value, it must be linked. Often it’s done by automation

a kind of short explanation about lookup & rollup. these field types and ‘count’ type as well, are computed, and must be ‘based’ on some linked field:

 

 

Hi ​@0800-grizzly, hope you’ve made some progress on your reporting. I believe ​@Alexey_Gusev already gave you a pretty solid way for automating the monthly records. That should work great. For the projections it really depends how you want it to work. I was thinking you could have an automation that would create new records in your “Finance Monthly” with a primary field of (YYYY-M-P) The P would be for projection. This is just to differentiate them from the other ones. You can link these records to all other non P records in your Finance Monthly table and do a rollup of the averages to get a total average of all months. Then you can just multiply that by a specific factor each month. If curious just let me know and I can go into more detail.

Terms & Conditions