Summarizing the responses of a repeat question

SimonLange — Thu, 28 Nov 2019 18:44:33 GMT

Hi there!

In my Survey I am trying to analyze the ground cover of a few indicator plants at various points.

I am using a 'repeat' to collect the different coverage values for every point but upon calculating (using the 'sum' formular), Survey123 is concatenating the values instead of adding them.

See the attached xls. form for more details on how the repeat is structured.

Can anybody help me out on this one?

Thank you!

Re: Summarizing the responses of a repeat question

IsmaelChivite — Sun, 01 Dec 2019 02:14:58 GMT

Hi.

Short answer: If you set the value of column bind::type in row number 17 to int, your sum function in row 21 will return a sum rather than a concatenation of strings.

Long answer: Working with calculate and hidden questions can get a bit tricky when working with functions like sum. If on top of that we add relevant statements things complicate even more. Your survey has both! So here are some tips:

Calculate and hidden questions always default to the text data type. This is why they always get stored as esriFieldTypeString and functions like sum think of them as text. In your case, you want your calculate question to be treated as an integer and be stored as an integer. For this reason, you want to set the bind::esri:FieldType to esriFieldTypeInteger (stores values in your feature layer in an integer column) and the bind::type as int (so it gets treated as an integer for calculations within your XLSForm). If you do this, you do not need to cast values to number in your XLSForm expressions and the sum function will sum numbers as opposed to strings.
The value of a non-relevant question is always set to null. This causes problems when you try to use mathematical functions. For example 2 + null is null. If you sum the values of a relevant question and a non-relevant question, you get a null; it looks as if the sum function would do nothing. The trick here is to use the coalesce function. Coalesce takes two inputs and returns the first input that is not null. For example, coalesce(${plant_coverage_1},0) will return the value on plant_coverage_1 if it is not null, otherwise it will return 0. Using this technique, you can safely add the ground cover of all the plants (even if some of them are non-present).

With the above in mind, you can simplify your XLSForm quite a bit. I attached a new simplified version of your form. Hope it helps!

Re: Summarizing the responses of a repeat question

SimonLange — Mon, 02 Dec 2019 12:31:38 GMT

Thank you very much - now it works!

Re: Summarizing the responses of a repeat question

Anonymous User — Tue, 20 Apr 2021 16:21:16 GMT

Thanks Ismael!! The response you provided a couple years ago is still helping folks like me today!!

topic Re: Summarizing the responses of a repeat question in ArcGIS Survey123 Questions

Summarizing the responses of a repeat question

Re: Summarizing the responses of a repeat question

Re: Summarizing the responses of a repeat question

Re: Summarizing the responses of a repeat question