Addition Calculations Not Working

1597
7
Jump to solution
10-02-2019 06:22 AM
MikalaRandich1
New Contributor II

Hello,

I have a large form where some fields that are calculated build on each other. I have some pretty basic addition calculations of previous fields I need some of the last fields to complete, that I cannot get to work. There are slightly more complicated conditional statement calculations that work fine, so I'm having a difficult time figuring out why the addition won't work.

In the attached xlsx file, I would like row 443 to sum rows 438 through 442 , row 450 to sum rows 445 through 449, row 457 to sum rows 452 through 456, row 464 to sum rows 459 through 463, and row 471 to sum rows 466 through 470. Currently, nothing appears in these fields, when the sums should be appearing. Any help would be greatly appreciated!!!

Thank you,

Mikala

0 Kudos
1 Solution

Accepted Solutions
JamesTedrick
Esri Esteemed Contributor

Hi Mikala,

It looks like your calculations are cascading (i.e., the lines 438-442 all are calculations themselves).  Are any of the original values ever blank (and thus NaN - Not a Number)?  If so, that would cause an issue with subsequent calculations as NaN+1 = NaN - the blank carries through the rest of the calculation chain.

One way to resolve this is to place each of the values in the addition in a coalesce(${q}, 0) function - that will provide a fallback value of 0 if the question is empty.

View solution in original post

7 Replies
DougBrowning
MVP Esteemed Contributor

It would help if you listed what it is doing and what you expect.

I do see that you are making fields a decimal then using int().  This may cut off the decimal places.  Also it should not be needed since it is already a number.

If that does not work I have found setting the bind::type field helps with + a lot and works better than using int() or number().  

Also not sure why you have multiple lists for yes/no.  You reuse those lists for multiple questions.

I would also look into groups and pages to organize this better.

0 Kudos
MikalaRandich1
New Contributor II

Hi Doug, thanks for your response. I updated the question to hopefully be more specific. I changed the field types to all be integer as well as put 'int' in bind::type, and removed the int() functions from rows 438 - 443 as a test, but row 443 still will not sum rows 438 through 442. As background, the original form was created in the web designer before pulling into Connect, which created the duplicate lists, and I will look into creating groups to better organize the form. 

0 Kudos
JamesTedrick
Esri Esteemed Contributor

Hi Mikala,

It looks like your calculations are cascading (i.e., the lines 438-442 all are calculations themselves).  Are any of the original values ever blank (and thus NaN - Not a Number)?  If so, that would cause an issue with subsequent calculations as NaN+1 = NaN - the blank carries through the rest of the calculation chain.

One way to resolve this is to place each of the values in the addition in a coalesce(${q}, 0) function - that will provide a fallback value of 0 if the question is empty.

MikalaRandich1
New Contributor II

Hi James, thanks so much for the suggestion! I thought I would have avoided this by entering "0" as the default for the preceding input questions, but the coalesce function solved the problem. Thanks again!! 

0 Kudos
Jing_Sun
Esri Contributor

Hello James,

I also found a method to convert empty value to 0 if the question is empty.

The idea is not set bind::esri:fieldType and bind::type for the select_one or calculate question so that the field type is string.

Then use 'number(${previous_question})' to reference the answer in the following question. If ${previous_question} is an empty value, number(${previous_question}) will be 0. 

This method has been working fine but I would like to know if this is a proper method? Will any update in the future alter the behaviour of number(empty value) = 0 ?

Thank you.

Sun

0 Kudos
JamesTedrick
Esri Esteemed Contributor

Hi Sun,

No, the conversion is not likely to change behavior.

Jing_Sun
Esri Contributor

Thank you very much! I have posted this method along with known limitations for others to reference.

Cheers,

Sun

0 Kudos