CountIf equivalent for Survey123

[Mondi_GIS]

I have a survey where I am capturing the height of trees in a repeat, and then later on in the survey, I calculate the average height of these trees and then the values for 20% above the mean, and 20% below the mean. Now I need to do a count of the number of trees that are more or less than 20% above/below the mean. 

Is this at all possible?

I have tried using the logic of

if(${height} > ${top_from_mean}, '1', if(${height} < ${lower_from_mean},'1', '0'))

inside the repeat, but this seems to write a static value to the field in the order that the data is captured, rather than a dynamic value that calculates as the average and top_from_mean values update. If I put that calc outside of the repeat it won't do the calc on the individual values and I won't get a count. 

Any suggestions? Would it be easier to do this in Arcade on the data after it is submitted? We did want to present the results as the survey is completed to pass/fail the sample plot. 

[DougBrowning]

The formula you have goes in the repeat.  But change your string '1' to a number 1.  Then set the bind:type field to int (otherwise it concats so 1111 instead of 4).

Then outside of the repeat use one of these formulas.

sum(repeat)  Returns the sum of all responses to a given question across repeats.  sum(${question})

count(repeat)  Returns the amount of responses to a given question across repeats.  count(${question})

See here for more  Formulas—Survey123 for ArcGIS | Documentation 

hope that helps

[Mondi_GIS]

Hi Doug,

I have many sum(repeat)s in my survey that work fine. The challenge here is not summing the repeat, the challenge is using the average of the repeats to validate the if statement inside the repeat. 

[DougBrowning]

I think I get it now.  This sounds like analysis you need to do after the fact.  Once you move to the next repeat the calcs will not fire until you go back into that repeat.  The other issue would be data changes would not recalc.  This happens to us.  User collects the heights.  Later in the web map there is some QA on the heights.  Say someone did 22 instead of 2.  If you fix this the average does not update and all your counts are now wrong.

You could do it in Arcade.  I have some code here.  Note as of now the lookup to the repeat will not work in Collector - only the web map.  It is coming sometime this year.

https://community.esri.com/thread/229709-are-the-arcade-featureset-functions-supported-in-collector 

[Mondi_GIS]

We need this to work and get calculated automatically after submission so that it can be viewed in a Survey123 feature report. Any suggestions?

I can't see how doing it in Arcade will address that? I'm not that familiar with it but my understanding is that it would only calculate "live" as a symbology or pop-up? Neither of these would help in the report or a dashboard?

[DougBrowning]

Sorry did not know you were doing reports.  I have not used much reports but I think you can run some stats on them.  Someone else may know more.

We do not do calcs in reports since we run the data through QA then do any calcs in script.  We had too many people running off with the data right away without checking it all.

Sorry I could not help more.

[Mondi_GIS]

Thanks... It is a tricky one! 

The feature reports want you to use the fields inside the repeat, and not reference a field outside of a repeat as far as I can understand. Very tricky problem!