Discussions

 View Only

  • 1.  Fiscal YTD Monthly query report

    Posted 01-11-2023 15:02
    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
    ------------------------------


  • 2.  RE: Fiscal YTD Monthly query report

    Posted 01-11-2023 16:13
    Edited by Doug Henning 01-11-2023 16:38
    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
    ------------------------------

    Original Message


  • 3.  RE: Fiscal YTD Monthly query report

    Posted 01-11-2023 16:29
    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
    ------------------------------

    Original Message