Survey123 Calculation incorrect after submission

679
4
04-18-2022 12:38 PM
Michael_VanHatten
New Contributor III

I am wondering if anyone has noticed their calculations in Survey123 being incorrect after submission of a survey? I just started noticing it with one of my surveys today (4/18) but I swear there was not this issue last week. 

I have a survey with a list of questions, the score of each question is averaged together at the end of the survey for a final average score. Questions can be skipped and thus the average calculation should reflect skipped questions. (10 questions answered = add all 10 questions / 10, 8 questions answered = add all 8 scores / 8). 

I am noticing that while doing a test on the Survey123 web interface, the math is correct (correct number of questions to divide by) the final average is correct, and when I hit "submit" I see the average drop. When checking the data on the backend of S123 I see that the math was done with the incorrect number of questions. The calculation seems to "work" prior to submission, after submission the math is incorrect. Has anyone had this issue? I am using a simple if then statement to ignore questions without a value greater than zero and add all questions with values greater than zero for my division. 

0 Kudos
4 Replies
DougBrowning
MVP Esteemed Contributor

My guess would be coalesce.  The web can be picky on adding up ''.  Often you have to use a if(0,1) type counting or do a coalesce to make sure it gets a 0 if they skip it.

coalesce(value1, value2)

Returns the first nonempty value. Supports only two values.

coalesce(${question_one}, ${question_two})

It would really help if you posted the actual formulas you used though.

Hope that helps

0 Kudos
Michael_VanHatten
New Contributor III

Doug,

Here is the snippet of the code used for calculating the total number of answered questions, this should give me the number I divide by for the average. 

if(${canopy_foundation_condition} !=0, 1, 0) + if(${canopy_station_deck_condition} !=0, 1, 0) and it keeps going for 26 questions. This works until submission then it just counts all 26 questions as having a value greater than '0'. 

The formulas are really long but if you want to see the whole thing I can send them. That number goes into this formula to calculate the average (count_average_condition). 

......... + if(${station_site_plum_irri_condit} >0, number(${station_site_plum_irri_condit}), 0) + if(${station_site_aircom_sump_condit} >0, number(${station_site_aircom_sump_condit}), 0)) div ${count_average_condition_sec2},2)

Thanks for the response.

 

0 Kudos
DougBrowning
MVP Esteemed Contributor

I think it is the if > 0 failing if it is ''.  This reads if > 0 - which is false since it is '' then it gets the ''. 

You can set the bind column to int or use coalesce(${question_one}, ${question_two})

 

I would try this 

coalesce(${station_site_plum_irri_condit}, 0)  and remove the if statements.

 

 Hope that helps

0 Kudos
Michael_VanHatten
New Contributor III

Doug,

Thanks for the help I looked into everyhting you mentioned and tried changing the calculations but had no luck. Then I realized something, the survey and the calculations behave differently between the IOS app and the web browser option. My calculation was looking for results that did not equal zero in a particular question. When I use app to submit a survey (which will be how my team uses the survey) for whatever reason the survey data comes through with the zeros from unanswered questions. When the survey is submitted through the online browser the same field is populate with nothing and the value remains blank, not '0'. Which resulted in the survey counting the record. I dont know why this is happening but I am happy that it works as designed in the mobile app. 

Thanks

0 Kudos