JenniferJuhasz
3 years agoQrew Cadet
Formula Query - How to return values within a Quarter or summarize data within a Quarter?
Hi,
Is there a way to build a query that follows this function, but for quarters, not year?
var text QUERY = "{7.OAF.'" & FirstDayOfYear([Month]) & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
Size(GetRecords($QUERY,"bru6bq226"))
Essentially, I want exactly what this current query does, but with the quarter specified. I do have a 'Quarter' field that's calculated like this:
If(
Month([Month])<=3,"Q4",
Month([Month])<=6,"Q1",
Month([Month])<=9,"Q2",
Month([Month])<=12,"Q3",
"")
But I'm not sure how to utilize it in my query to specify the date range.
I think I need this formula function:
LastDayOfPeriod([Payment Date], [Quarter Length in Days], [Fiscal Year Start Date]) returns the last day of the quarter in which the Payment Date falls.
But I keep getting Syntax errors when I try to input Quarter Length in Days. I tried to specify the # of weeks instead, but again, I couldn't quite get past the Duration p, Date r without generating errors.
Can you help point me in the right direction with these?
Thank you!
------------------------------
Jennifer Juhasz
------------------------------
Is there a way to build a query that follows this function, but for quarters, not year?
var text QUERY = "{7.OAF.'" & FirstDayOfYear([Month]) & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
Size(GetRecords($QUERY,"bru6bq226"))
Essentially, I want exactly what this current query does, but with the quarter specified. I do have a 'Quarter' field that's calculated like this:
If(
Month([Month])<=3,"Q4",
Month([Month])<=6,"Q1",
Month([Month])<=9,"Q2",
Month([Month])<=12,"Q3",
"")
But I'm not sure how to utilize it in my query to specify the date range.
I think I need this formula function:
LastDayOfPeriod([Payment Date], [Quarter Length in Days], [Fiscal Year Start Date]) returns the last day of the quarter in which the Payment Date falls.
But I keep getting Syntax errors when I try to input Quarter Length in Days. I tried to specify the # of weeks instead, but again, I couldn't quite get past the Duration p, Date r without generating errors.
Can you help point me in the right direction with these?
Thank you!
------------------------------
Jennifer Juhasz
------------------------------