“NaN” appearing in calculated value. How do we get rid of this?

[hussaisa]

Dear @Kal_Lam,

Please find attached xlsform . Everything is fine except ;
1- “Cumulative support” doesnt show the sum until all the calculations have some value while intent is to sum up even few of the calculations are not entered . .means if “Total_outside” has no value it should consider it 0 and same goes to others.
calculate cumulative ${Total}+${Total_outside}+${Total_one_within}+${Total_one_outside}

2- “NaN” is appearing in three of the calculations .

Conditional_Clacluations.xlsx (11.4 KB)

Appreciate if you can please help.

[Kal_Lam]

Hi @hussaisa,

Would you mind checking the xlsform. It should now work. Kindly please be informed that you could use the coalesce() function to stop seeing the NaN as you are currently seeing.

Reference xlsform:
Conditional_Clacluations.xlsx (11.6 KB)

Have a great day!

[hussaisa]

@Kal_Lam Wonderful . Thanks a million .

[Faith]

@Kal_Lam Hello,

I need urgent help, my total scores calculation are blank. My xls form is below, possible you helped
aBQEt5Kv5SXpz3zcHwbQMJ (9).xlsx (12.3 KB)

thanks,
Faith

[Kal_Lam]

@Faith, FYR

Form error on questions

@Faith, maybe you could have a look at this post discussed previously that should help you add variables (not showing NAN): You could also have a look at our support article Adding Skip Logic to lean how to manage skip logics within a survey project.

[Faith]

@Thanks for the feedback though I am still sruggling, maybe you can guide me on how exactly I can go about it with the formular of getting total scores for each submitted form.My xls is below

aBQEt5Kv5SXpz3zcHwbQMJ (16).xlsx (12.6 KB)

[Kal_Lam]

@Faith, FYR:

Form error on questions

@Faith, the coalesce should be used as follows: coalesce(${calculation},0)+coalesce(${calculation_001},0) instead of coalesce(${calculation})+coalesce(${calculation_001}) You could still check out the workaround shared previously.

[Faith]

@Kal_Lam Thanks this has worked

I used this:
coalesce(${calculation},0)+coalesce(${calculation_001},0)+coalesce(${calculation_002},0)+coalesce(${calculation_003},0)+coalesce(${calculation_004},0)+coalesce(${calculation_005},0)+coalesce(${calculation_029},0)+coalesce(${calculation_006},0)+coalesce(${calculation_007},0)+coalesce(${calculation_028},0)+coalesce(${calculation_008},0)+coalesce(${calculation_009},0)+coalesce(${calculation_030},0)

One more question;

For my total score to show: I use this:

Total Score is:${sum}
this formular am using tp pick number for total score but its blank

[Kal_Lam]

@Faith

Total Score is:${sum}

Maybe you will need to ensure that you have a variable sum in your form that calculates the total.

[soso]

Hello, I also have the same issue. coalesce() still returns NaN when the field contains 0. Please help

[Kal_Lam]

Maybe you will also need to check the other source of calculation.

[22Derebe]

@Kal_Lam , can we change this in an already built for with data for the past three years?

Thanks, Derebe

[wroos]

Hello @22Derebe
I would also recommend to always avoid own variable names which exist as KoBo/ODK code names, e.g. model or sum (function).

[22Derebe]

@wroos did not get your point. my question is can I replace the form with submission without losing data already submitted? I have a form with NaN issue which has 489 records. I have attache the XML form and the deployed form screenshot.

[wroos]

Hello @22Derebe,
I think you could change the formula without damaging submitted data. But this will not update already submitted/calculated data.

It could be possible to open/edit all submissions to activate the new calculation. You can try this by

• clone the project
• submit a test case with the old form version
• deploy the new form version with the coalesce
• open/edit and save the previous case.
• check the new case data.

You might also try to use the bulk edit option.

[22Derebe]

@wroos Thank you so much, as you said I have to update each submitted data again in order for the formula to work on previously submitted data.

Appreciated!