Easy way to do rolling 12 monthly average chart ?

Can you describe your table relationships?

I have not created any relationships for this part. I have never used relationships to analyze data. I am confused why that would matter.

Ok, so just 1 table at this time. can you describe your data in the one table.  Is there just 1 record per month or does each month have many records.  If there are many records for each month, what does each record within a month represent?

Trevor, I'll leave QBCoach to give you a specific answer on this but will just explain about relationships as these are very powerful things in Quickbase as they allow you to natively do lots of things.

An example being the max function. If you are adding into a table say a record each month containing all your sales stats for each site in your organisation. You could setup a relationship one site can have many sales stats.

With this you can then create an automatic report to show sumarised sales data. Once you have create the sales data and sites relationship go to table to table relationships and add a summary field. Then select max (now you have 2 options, you can either select record id or date), ill use date and then name max date 1, click create field. then add another field this time exactly thew same but at the bottom you choose the option where date is not equal to max date 1, call it max date 2. then the same again but where it is note equal to max date 1 and where it is not equal to max date 2.

Keep going till you have 12 fields.

You then have some choices, you can either create summary fields totalling certain figures from your sales data and build a report in the sites table or you can via the table relationship create lookup fields for max date 1 - 12 and build you report in the sales data table using the filter where date is equal to max date 1 or date is equal to max date 2 etc.

This will allow a rolling 12 month report. as each time you add a new record with a more recent date the dates work their way down towards max date 12 and then drop off once you have 12 records with a greater date.

Note if using multiple sites you will need to also add Site Name is equal to related site name or something along those lines to each of your max date fields as otherwise you wont get just the specific results for your site.

You can then create a total or average for a field in the same way as above, you can then create lookup fields to pull this down to your sales data table and create a field called sales average and then sum the 12 lookup fields and divide by 12, you can then add this to your graph and it will give you a 12 month average line. Then your actual sales data line would be either above or below the rolling 12 month average.

I can think of about a dozen uniquely different ways to do this but they all involve script. I have other work to do but when I get a chance I will show you a way to do this. Here is some time series data from our pals at the Census Bureau:

Monthly & Annual Retail Trade ~ Time Series Data
https://www.census.gov/retail/marts/www/timeseries.html

Monthly & Annual Retail Trade ~ Time Series Data ~ Grocery Stores
https://www.census.gov/retail/marts/www/adv44510.txt

I uploaded the Grocery Store data into this application:

Moving Average
https://haversineconsulting.quickbase.com/db/bkks9u2da?a=td

When I get a chance I will show you how you can use script and the QuickBase API to calculate [Grocery Stores Sales 12 Month Rolling Average] from [Grocery Stores Monthly Sales] and then generate a chart of both fields over time.  You will weep with joy at the simplicity of how to do this.

I wrote a quick script to do the calculation but I think I am going to change the data as I had too many points for the chart and I just restricted the start date for now. I will come back to this later.

Moving Average
https://haversineconsulting.quickbase.com/db/bkks9wfjt

This looks exactly what I am looking for but cant see how you did it.  I am waiting to weep with joy.

Well free to contact me off world using the information in my profile if you want to pursue a solution for your specific needs. Beyond creating a working demo and posting the essential code I don't know what else to say if you don';t have a specific question.

Sorry I can see that you have done it but I can not see essential code. I may not know how or I dont have permission.