Help with Dynamic Calculations (re-posted with answer included)

Support
1

QUESTION
I designed a Baseline Survey to collect data, tested a couple of times and
worked prefect. However I am trying to improve it as I learn more about
this computer language but I came across a NaN error when trying to add the
results of multiple answers. Most of those answers are not required fields
so no data is entered and I noticed that in order to get the total instead
of the "NaN error", at least a "0" must be entered on each answer.
How can I get around this issue? Can I force the answer to be "0" if no
data is entered? How can I do this?

I attached my form for you to check and highlighted all the fields involved
in orange. The calculation formula is on D169.

ANSWER
Hi Gerry,

Best to ask this question in one of the fora so that others can benefit.
There are a few ways to deal with this:

  1. You can add a type='calculate' after each question: if(string-length(${land_income})>0,
    ${land_income}, 0) or coalesce(${land_income}, 0). This sets the
    calculated item to 0 if the question has not been answered. In your final
    calculation, you add up these calculated values instead of the question
    values.
  2. [easiest] You can add a 'default' column to your XLSForm and make 0
    the default value. This won't completely eliminate NaN, because when the
    user clears the value, the default value will never come back. The other
    potential problem is that it won't allow you to differentiate between an
    unanswered question and an actual 0 income in your analysis.
  3. You can make use of the coalesce() function in your final
    calculation. So simply change total_income to: coalesce(${land_income},
    • coalesce(${migrant_income},0) + etc.

I would probably choose #1 or #3. #1 is more work but the formulae will be
easier to troubleshoot.

Baseline_Survey.xlsx (69 KB)

1 Like