Skip to main content
Question

Fiscal YTD Monthly query report

  • January 11, 2023
  • 0 replies
  • 89 views
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
------------------------------
    This topic has been closed for replies.

    Forum|alt.badge.img+6
    • DougHenning1
    • Quickbase Staff
    • January 11, 2023
    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
    ------------------------------
      Terms & ConditionsAccessibility statement