Hi @Kal_Lam,
I have a problem with data calculation. I have a form with repeating groups with some calculations. The data is calculated using ‘coalesce’. At the end, I use ‘sum’ to calculate the data from all repeating groups and output the result to the form as Count of kits : ${calculate_total_kits}
But if the data is missing, I get a NaN error. How can I fix it?
I have read previous advice about this problem, but none of it helped me.
Hi @palii and welcome to the community!
That is because In the ODK Form Logic, unanswered number questions are nil. That is, they have no value. When a variable referencing an empty value is used in a math operator or function, it is treated as Not a Number (NaN). The empty value will not be converted to zero. The result of a calculation including NaN will also be NaN, which may not be the behavior you want or expect.
To convert empty values to zero, you can use either the coalesce() function or the if() function.
coalesce(${potentially_empty_value}, 0)
if(${potentially_empty_value}=“”, 0, ${potentially_empty_value})
Can you share your calculation with us, so we can take a better look at it?
Wow, I can attach xlsfile. Could you take a look at it?
test form.xlsx (80.8 KB)
I had a look at your form; its rather complicated… but I deduce you are basically trying to have a repeat loop with a bunch of numeric inputs (some of which may be left blank), calculating a subtotal for each iteration, and then summing of all these subtotals for a grand total. Correct?
I distilled this down to something a lot simpler to follow. Please take a look and have a play with this form and see if you can adapt it to your needs. AFAICT it works - you can test it under Enketo here: XLSForm Online
RepeatSubtotals.xlsx (14.5 KB)
Yes, you’re right. I have a form with a repeating group, inside of which there is another repeating group. This form does have a bunch of input data that is calculated into the main three categories at the end of the form.
If you simplify the form, it looks exactly like the file you sent. However, I do everything the same way, but when I fill out my form, I still have NaN if the value is empty.
