Forum Discussion

JenniferJuhasz's avatar
JenniferJuhasz
Qrew Cadet
3 years ago

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
------------------------------

2 Replies

  • The LastDayofPeriod functions would not be used for calendar Months.  those are typically used for 2 week payroll cycles for example.

    Since you already have a field say fid 99 for the Quarter, you can just add in an extra AND to your query

    AND {99.EX.'" & [Quarter] ....  

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • JenniferJuhasz's avatar
      JenniferJuhasz
      Qrew Cadet
      Hi Mark,

      This worked perfectly! Thank you!!  Eventually I'll get the hang of these formula queries...  I appreciate your help.

      Jen

      ------------------------------
      Jennifer Juhasz
      ------------------------------