topic Re: Calculating Yearly Quarters in ArcGIS Survey123 Questions

Calculating Yearly Quarters

PaulPanehal — Sun, 12 Dec 2021 09:24:55 GMT

Hello all,

I wanted to see if anyone had an idea for auto-filling a text field with the correct year quarter based on a series of IF-THEN statements. I am having an error.

This is what I presently have:

IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),1,1),${INSPECTION_DATE}<=DATE(YEAR(${INSPECTION_DATE}),3,31)),"Q1",IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),4,1),${INSPECTION_DATE}<=DATE(YEAR(${INSPECTION_DATE}),6,30)),"Q2",IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),7,1),${INSPECTION_DATE}<=DATE(yEAR(${INSPECTION_DATE},9,30)),"Q3",IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),10,1),${INSPECTION_DATE}<=DATE(YEAR(${INSPECTION_DATE}),12,31)),"Q4")))))
‍

Basically, there are a series of nested IF statements where I am trying to define the range for each quarter of the year. I am using a field that uses a TODAY() [${INSPECTION_DATE} is TODAY()] function to define the correct year for this calculation. Of course this statement works well within Excel itself, but not so much for Survey123 where it cannot parse through my statement.

I think it has everything to do with not having the AND(), DATE(), YEAR() functions added into its framework, or having different functionality. I may also be overwhelming the parser.

My ultimate question is if there is a way to calculate a range of dates that are based on set quarters (month and day), and auto-fill a text field with the correct quarter (Q1, Q2, Q3, Q4) based on the result. Like I said, the above function works in Excel, but not for the XLSForm syntax.

This is all there really is to it. This field is intended to be read-only and auto-calculated for field inspections.

Re: Calculating Yearly Quarters

JamesTedrick — Mon, 29 Jan 2018 18:42:43 GMT

Hi Paul,

Though Survey123 Connect does use Microsoft Excel to author surveys, it does not use Microsoft Excel functions. You'll find information on the functions it does support at Formulas—Survey123 for ArcGIS | ArcGIS .

ON determining the quarter of a year, there are a couple of ways to accomplish this. The most important function in this case is the format-date() function, which you can extract the month value, which then can be used in either calculations or regular expression evaluations. Below is a sample calculation to generate "Q1", "Q2", "Q3", "Q4":

concat("Q", int(format-date(${d1}, '%n') div 4 + 1))

- format-date generates the month as a number

- the month divided by 4 + 1 gets us a value from 1.333 to 4.000 that aligns to the quarters

- the int function drops off the fractional component

- the concat function joins the letter Q with the month value.

Re: Calculating Yearly Quarters

PaulPanehal — Mon, 29 Jan 2018 21:39:59 GMT

Thanks for getting back to me James,

I have looked into your formula, and it was close to what I needed. I looked through other solutions for excel, and came up with this formula.

concat("Q",int((int(format-date(${INSPECTION_DATE}, '%n'))+2) div 3))‍

This ensures my quarters are:

Q1: Jan 1 - Mar 30
Q2: Apr. 1 - Jun 30
Q3: Jul 1 - Sept 30
Q4 Oct 1 - Dec 31

I appreciate you giving me the right footing. This will not somehow be affected by epoch timing, or some other standard, correct?

Re: Calculating Yearly Quarters

TKSHEP — Tue, 06 Aug 2024 21:08:03 GMT

I am trying to do this exact formula, how would I do it if my Q1 is Oct to December?

Re: Calculating Yearly Quarters

TKSHEP — Tue, 06 Aug 2024 22:27:47 GMT

I am trying to do this exact same thing, how would I calculate it if the Q1 starts in October?

Re: Calculating Yearly Quarters

TKSHEP — Fri, 09 Aug 2024 13:31:30 GMT

@JamesTedrick Is there a way to do this exact thing with the FY Q year starting in October? Thank you so much!

Re: Calculating Yearly Quarters

TKSHEP — Fri, 09 Aug 2024 13:32:59 GMT

@PaulPanehal is there any way to do this exact thing with the FY Q starting in October? Thank you for any help you can provide.