Qrew Commander
11 months ago

# Rolling 3 Month Average

Here is a report formula that returns a rolling 3 month average. FID 7 is [Bill Creation Date], FID 9 is a field I am using to filter records, and FID 18 is the currency field that is being averaged. If you want to do 6 months average, just change the -2's to -6's for the month string.

var text QUERY = "{7.OAF.'" & ToDate(

//create 2-digit month
If(ToNumber(Month([Bill Creation Date]))=2,"12",
If(ToNumber(Month([Bill Creation Date]))=1,"11",
If(ToNumber(Month([Bill Creation Date]))<10,"0" & ToText(Month([Bill Creation Date])-2),
ToText(Month([Bill Creation Date])-2)))) &

// first day
"/01/" &

//create 4-digit year
If(ToNumber(Month([Bill Creation Date]))<=2,ToText(Year([Bill Creation Date])-1),
ToText(Year([Bill Creation Date]))))

& "'}AND{7.OBF.'" & LastDayOfMonth([Bill Creation Date]) & "'}AND{9.CT.'TEXT_STRING_FILTER'}";

SumValues(GetRecords(\$QUERY),18)/Size(GetRecords(\$QUERY))

Hope you find this useful.

P.S. @Sharon Faust thanks for sharing your tips on report formulas!

------------------------------
------------------------------

### 2 Replies

• @Adam Keever This is a cool use for query formulas.

You may want to consider using the AdjustMonth() function to create the 2 month earlier date for the query.

So,

var text QUERY = "{7.OAF.'" & AdjustMonth(FirstDayOfMonth([Bill Creation Date]),-2)

& "'}AND{7.OBF.'" & LastDayOfMonth([Bill Creation Date]) & "'}AND{9.CT.'TEXT_STRING_FILTER'}";

SumValues(GetRecords(\$QUERY),18)/Size(GetRecords(\$QUERY))

-Sharon

------------------------------
Quick Base Junkie
Quick Base Junkie
https://quickbasejunkie.com
------------------------------

• Ah, thanks @Sharon Faust. That is so much better, plus it helped with another summary I was working that is by rolling quarters!

Shameless plug here, check out Quickbase Junkie for lots of amazing tips to help you be more productive in Quickbase.

------------------------------