Formula Query to create Line Chart filtered by the current logged in user
Hi all, I am working on a formula query to try to build a line graph that will show cumulative sales for the current user in the current year and previous two years. I had this working without the by user filter. Here is my formula…. Just to help explain fid 19 is a lookup field that is a user. That is our sales person for the project. fid 66 is a formula field and in it is User() to get the current logged in user. For some reason this is pulling blank lines (all 0's) and not pulling in data.
//fid 7 = [Billing Amount]
//fid 11 = [QuickBooks Invoice Date]
//fid 18 = [Opportunity Type]
//fid 71 = [Current User Is Team Member (=1) - Lookup]
//fid 19 = [Project Solutions]
//fid 66 = [Current User]
var text QUERY = "{11.OAF.'" & FirstDayOfYear([QuickBooks Invoice Date]) & "'}AND{11.OBF.'" & LastDayOfMonth([QuickBooks Invoice Date]) & "'} AND {19.EX.66}";
SumValues(GetRecords($QUERY),7)
------------------------------
Ivan Weiss
------------------------------