Forum Discussion

AdamKeever1's avatar
AdamKeever1
Qrew Commander
2 years 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!



------------------------------
Adam Keever
------------------------------

  • QuickBaseJunkie's avatar
    QuickBaseJunkie
    Qrew Captain
    2 years ago

    @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
    ------------------------------

    • AdamKeever1's avatar
      AdamKeever1
      Qrew Commander
      2 years ago

      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.



      ------------------------------
      Adam Keever
      ------------------------------