I am totaling the questions by section and it is giving me NaN

[amailuk]

Well, it has developed another hiccup…

I am totaling the questions by section and it is giving me NaN.

The previous are now working fine leaving blanks where the question is not answered. Now, totaling the different questions is giving me NaN. All the questions have to be answered (AND CORRECT) then NaN clears and it offers the correct total!.

Any insights?

Thanks in advance

[hakan_cetinkaya]

Hi @amailuk,

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})

[Kal_Lam]

@amailuk, here is a sample of how it works (which has been discussed in a previous post):

Question about the max function and missing value

You could do it as outlined in the image shared below. In the survey tab of your xlsform: [Image 1] Data entry screen as seen in Enketo: when all the variables are filled [Image 2] Data entry screen as seen in Enketo: when some of the variables are missing [Image 3] Reference xlsform: Maximum.xlsx (9.7 KB)

[amailuk]

Hey @hakan_cetinkaya, I appreciate your response.

I am familiar with the NaN issue and the fact it is normally cleared by the coalesce function.
In my case it has not worked with previous options. Hence the workaround that @Kal_Lam advised that solved my headache, by using the relevant column.

I now have to total the earlier calculated variables and they are not working. Even after using your suggestions, copy-pasting them in case it was error on my part.

I’ve attached the XLS form that has the info to make sense of.

zer-Assessment DBM 633 Entrepreneurship & Business Consultancy.xlsx (17.1 KB)

[amailuk]

here’s the document challenging me

zer-Assessment DBM 633 Entrepreneurship & Business Consultancy.xlsx (17.1 KB)

[Kal_Lam]

@amailuk, I would use it as coalesce(${c1}, 0) + coalesce(${sum2}, 0) + coalesce(${c3}, 0) + coalesce(${sum4}, 0) + coalesce(${c5}, 0) instead of using it this way coalesce(${c1}+${sum2}+${c3}+${sum4} +${c5}, 0).

[amailuk]

Am so grateful, that has given me a break! Challenge finally resolved.

Thanks @Kal_Lam

[amailuk]

Thanks @kal_Lam, you’re a lifesaver!

I followed through with what you suggested, it worked!!!
It introduces a ZERO, but that I can live with.

Much obliged…

[Kal_Lam]

@amailuk,