Skip to main content
Question

Rolling 3 Month Average

AdamKeever1
Forum|alt.badge.img+2

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

      This topic has been closed for replies.

      QuickBaseJunkie
      Forum|alt.badge.img+15

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

      Terms & ConditionsAccessibility statement