Forum Discussion

MichaelPeoples's avatar
MichaelPeoples
Qrew Member
2 years ago

Fiscal YTD Monthly query report

Good Afternoon,

I have a made a query summary metrics table that summarizes projects submitted per month and costs per month.  Then the table also has a YTD field..Based on Quickbase Junkie's Easy Metrics Table using Formula Queries youtube  video. 

 This is working great, but I would like to have a Fiscal YTD calculated (Sept. 1) instead of calendar YTD.    I have changed the fiscal year date in the App settings.  

I was curious how to reference Fiscal Year (In place of FirstDayofYear ) in the query or how to change my formula to  run from Sept. 1-Aug. 31

Thank you!

Here is my current YTD query formula that is working:

var text QUERY = "{1.OAF.'" & FirstDayOfYear([Month]) & "'}AND{1.OBF.'" & LastDayOfMonth([Month]) & "'}";

SumValues(GetRecords($QUERY,"*******"),8)

------------------------------
Michael Peoples
------------------------------
  • DougHenning1's avatar
    DougHenning1
    Community Manager
    2 years ago
    This should work- it adjusts the beginning date to the previous year if needed:

    var text beginDate = IF(Month([Month]) < 9, "9-1-" & (Year([Month]) - 1), "9-1-" & Year([Month]));
var text QUERY = "{1.OAF.'" & $beginDate & "'}AND{1.OBF.'" & LastDayOfMonth([Month]) & "'}";​​

    EDIT: Updated for missing $

    ------------------------------
    Doug Henning
    ------------------------------
    • MichaelPeoples's avatar
      MichaelPeoples
      Qrew Member
      2 years ago
      Thank you very much Doug!   That worked great... 

      For others who might use this formula I had to add a $ before the beginDate to apply the formula on my page.  See the updated formula below.  



      var text beginDate = IF(Month([Month]) < 9, "9-1-" & (Year([Month]) - 1), "9-1-" & Year([Month])); var text QUERY = "{1.OAF.'" & $beginDate & "'}AND{1.OBF.'" & LastDayOfMonth([Month]) & "'}";​​

      ------------------------------
      Michael Peoples
      ------------------------------