Rolling forecast

[Yvan_Belier]

Hi all,
I’ve almost finished our CRM, but I’m totaly stuck to have a rolling forecast.

To simplify it as much as possible:
I have an “OPORTUNITIES” table. Each record has a “VALUE in €”, a “CREATED DATE” and a “CLOSING DATE”.
I would like, on a second table named “ROLLING”, where each record is a “DATE”, to cumulate all the ‘OPORTUNITIES’ records witch are open at a “DATE” (inbetween “CREATED DATE” and “CLOSING DATE”) in order to have the rollup of all “VALUES in €” per day.

–> Having a rolling forcast of my oportunities within time.

Thanks a lot

[Bill_French]

Yvan_Belier:

I’ve almost finished our CRM, but I’m totaly stuck to have a rolling forecast.

I don’t really have much to offer on the CRM axis, but I did manage to create something similar using the Script Block and machine learning for financial forecasting.

[Yvan_Belier]

Hello Bill,

and thank you for the answer. In fact I’ve red your post before sending my question to the comunity.
Unfortunately it’s not quite close from what I’m looking for, which is actualy way easier than your great machine learning script.

Here is an ilustrated example:

Here is what my two tables in Airtable look like:

What I need is a formula/ script / automation to fill up the PIPLINE colum
PIPLINE = the sum of all open oportunities over time

Hope it helps…

Thanks a lot for the support
It’s the last feature I need to get my CRM Operational for my company

[kuovonne]

Thanks for the images. I noticed that your dates are formatted day/month/year. Are the months of October and November supposed to include A? The text says they should, but the dates and sums indicate otherwise.

You cannot accomplish these calculations using only formula fields. You could get the pipeline sums by completely with a script, but a better method is to use linked records and roll ups. You also need a method of creating and maintaining the proper links. That could be done using automations with a Scripting action.

The script would examine the dates of the opportunity, calculate all the weeks/months that it should link to, find those weeks/months in the pipeline table, create them if they don’t exist, and finally create the links.

You would also need to determine how to trigger the automation—every time either of the dates change or some other method.

Feel free to reach out if you need someone to write this script for you—I’m an Airtable consultant and writing scripts is my specialty.

[Yvan_Belier]

Hello Kuvonne,

Indeed the DD/MM/YYYY is the french date format, but we can us the US standard for the example it’s fine.
And yes you are right I’ve made a mistake with the October and November month for A when I’ve writen my example.

You’ve got it all, I was sure I need a script, unfortunately I have no skils in such domain.