Calculate a rolling average

[paulmatthijs]

Hi all,

I’m looking for a way to calculate a revenue prediction based on all records in a table.

Table 1: All orders, with a weeknumber
Table 2: Weeks, with linked orders and revenue. The revenue is rolled up as {Revenue} and the week is {Week}.

In Table 2, I’d like a field that sums up the revenue up to that week. So, record 12 (for week 12) should roll up all weeks before 12 but not those afterwards. Even nicer would be to roll up only the X weeks prior to {Week}.

I cannot get my head around how to roll up the revenue conditionally. Any suggestions would be very welcome.

[Elias_Gomez_Sai]

So you want a subtotal till the specific week of Table 2? And what about the average on the title? :thinking:

[Jeremy_Oglesby]

There’s not an easy way to do this, since database tables have no concept of “records prior to this record” like a spreadsheet does.

I think you could try to do something like this:

• Create a field for each week of the year in Table 1; each one will be a formula field that checks if the week for that record is on or before that week of the year - if so, it gets a value of 1, if not, a value of 0
• In Table 2, create fields that lookup each of those fields values (you’ll want to hide all of these fields in each table)
• In Table 2, you can now create a view for each week of the year that filters based on the value of the lookup fields. Filter where Week 27 = 1 will show only records that have a value of 1 in the “Week 27” field, which should be records for weeks 1-27. Now the summary bar that sums or averages them all will show a “running” sum/avg up to that point.

[W_Vann_Hall]

As @Jeremy_Oglesby says, there’s no easy way to do this.

The difficult ways aren’t all that difficult, once you wrap your head around the process.

You can find some pointers here and here, as well as detailed instructions and sample code here. Don’t hesitate to ask if you have problems – it’s certainly nothing intuitive!