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 ...
Forum Discussion
This should work- it adjusts the beginning date to the previous year if needed:
EDIT: Updated for missing $
------------------------------
Doug Henning
------------------------------
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
------------------------------
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
------------------------------
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
------------------------------
