topic Re: Sum multiple fields in Survey123 report in ArcGIS Survey123 Questions

Sum multiple fields in Survey123 report

ZenMasterZeke — Fri, 08 Nov 2024 13:51:06 GMT

I'm trying to create a Survey123 report template that sums the values from three different number fields, but can't figure out how to do this. I can get the sum of each individual question, but not all three at once.

Each field asks the same question, but one for male, one for female and one for children. This code will give me the sum of all records for one field:

${#MySurvey | stats: “sum, male_interact, m_sum”} ${m_sum}${/}

But what I want is more like:

${#MySurvey | stats: “sum, (male_interact, female_interact, child_interact), mfc_sum”} ${mfc_sum}${/}

Is there a way to do this in a report? Thanks.

Re: Sum multiple fields in Survey123 report

PalomaGómezGonzález — Thu, 07 Nov 2024 16:20:58 GMT

I have a similar situation

I would like to have a report template that sums the values of three different numeric fields: num_plate1 + num_plate2 + num_plate3.

I have tried with this code but it does not work:

${Revisio_senyals_Bergueda | where: “(serie_placa1 = 'd39/2' OR serie_placa2 = 'd39/2' OR serie_placa3 = 'd39/2') AND (estat_placa1 = 'bon_estat' or estat_placa2 = 'bon_estat' or estat_placa3 = 'bon_estat')” | stats:"sum,(num_placa1 + num_placa2 + num_placa3)"}

Does anyone know how to get it?

Thanks

Re: Sum multiple fields in Survey123 report

ZenMasterZeke — Fri, 08 Nov 2024 13:51:31 GMT

FWIW: I know I could do this with a field in the survey itself, but for now, working with the data I have

Re: Sum multiple fields in Survey123 report

ZhifangWang — Thu, 14 Nov 2024 11:16:50 GMT

Hi @ZenMasterZeke ,

Assuming there are 3 records in your layer:

For male_interact, the value of the 3 records are: 1,2,3
For female_interact, the value of the 3 records are: 4,5,6
For child_interact, the value of the 3 records are: 7,8,9

Can you please confirm the expected result is 1+2+3+...+7+8+9=45?

Re: Sum multiple fields in Survey123 report

ZenMasterZeke — Thu, 14 Nov 2024 19:36:19 GMT

Thank you @ZhifangWang. Yes, that's what it should be. I just can't figure out how to get 6 + 15 + 24 = 45, the sum of each group sum, if that makes sense.

Re: Sum multiple fields in Survey123 report

ZhifangWang — Mon, 09 Dec 2024 07:27:57 GMT

Hi @ZenMasterZeke ,

The `stats` syntax utilizes the outStatistics parameter of the /query operation, and can only calculate statistics for one field. The # sign means there are multiple values here, and you can iterate each one in the report.

In your case, since you can ensure there is only one number (not multiple values) in the stats result, so the syntax can be simplified from ${#MySurvey | stats: “sum, male_interact, m_sum”} ${m_sum}${/} to ${MySurvey | stats: “sum, male_interact}. Notice that the # pound sign has been removed to indicate there is only one value in the result.

You can find examples in this help topic: https://docdev.arcgis.com/en/survey123/browser/analyze-results/featurereportqueries.htm

Hence, you could try the following syntax to add the 3 numbers: ${(survey|stats:"sum, male_interact")+(survey|stats:"sum, female_interact")+(survey|stats:"sum, child_interact")} (my survey layer name is `survey`).