Setting up a rolling sum based on date

Katrina_Burns

Hello,

We are trying to set up a beast mode or ETL Formula that will be able to calculate a rolling average of leads based on the last 30 days and the last 90 days. Ideally we would want to be able to compare the average number of leads for the last 30 days to their rolling 90 day average to see if the client's lead generation is on track. This ultimately will be used to set up a histogram to see which clients are on track and which aren't, but working on laying the groundwork currently.

The challenge for me in setting this up is our data is separated by client as well as date. (sample data attached). I've grouped this data by date in an ETL so we can get a count of leads for each client for a given day, but I am having trouble with writing the formula to make a rolling average. Is there a better way to do this?

Screenshot 2025-02-25 at 1.25.36 PM.png

Manasi_Panov

Hello @Katrina_Burns,

First, check this video from Mark Snodgrass.

https://youtu.be/sLAy-9pu0VM?si=VoQmp_zzjuiQry4D

You can use a Variable to showcase the Rolling Average for all customers or just a selected one. I went a step further and created three variables and now I can see the Rolling Average by country, product, or industry, whether I want the full picture or just a selection.

If you need further explanation, please let me know.