Select to view content in your preferred language

Only calculate relevant fields

825
7
Jump to solution
03-27-2023 05:55 AM
Philine
New Contributor

Hi everyone,

I have a group of questions containing a few yes/no questions, which are dependent on the previous question. Once a question group has been answered I should get a list of suggestions ranging from A - F. Once all group questions have been answered I would like to calculate which answers have been suggested the most and be able to pass on a result based on that. 

I am able to show the answers to a group of questions by using if(selected(${question, 'Yes'), 'Yes', 'No') and the relevant field, but I don't seem to be able to calculate this. When using calculations it seems to add all the questions instead of just the selected questions of that group.

See example below, where bold defines the answer given.

I hope this makes sense. 

Thanks so much in advance.

 

Question 1     ABCDE
Yes:Question 1.1         
 Yes:Questions 1.1.1        
  Yes:    11111 
  No:    11100 
 No:     10000 
No: Questions 1.2         
 Yes:    11001 
 No:     10000 
            
Question 2          
Yes:Question 2.1         
 Yes: Questions 2.1.1        
  Yes:    11111 
  No:   11101 
 No:     11000 
No: Questions 2.2         
 Yes:    11101 
 No:     10000 
            
            
     Total21111 
0 Kudos
1 Solution

Accepted Solutions
DougBrowning
MVP Esteemed Contributor

Looks like my guess was correct.  You are trying to add together "".  I would remove the relevant from lines 37-47.  It should calc to 0 then your final total will use that 0 instead of "".

Also why are you using '1' and '0' all over.  Change all of these to real numbers.  Adding strings is not going to work out well even when using number() or int() later.  Also this makes you attribute table all wrong.  Change to just 1 and then set the bind type column to int. Or set to integer then set appearance to hidden. That should do it.  I would also bind the final scores to int or better change the type to int then make then read only.

In the end you are trying to add strings is the issue.

Hope that helps

View solution in original post

7 Replies
DougBrowning
MVP Esteemed Contributor

Not sure I follow but my guess is that when a field is not relevant it changes to empty string "".  So a non relevant field will never have a value other than "".  If in the form if you try to add this up it will break since "" is not a number.  If you are counting it is better to use a type hidden and get it out of the group that has the relevant in it.  That way it was always calcualte.

Hope that is it

0 Kudos
Philine
New Contributor

Hi Doug,

thanks for the quick reply!

I probably haven't explained it properly. Attached an example question group.

I am currently showing results through the relevant field. But I cannot add this up across the different question groups as mentioned above. When I start calculating the field (row49-50) I am obviously doing something wrong since it should match what is shown in the answers above and not adding the numbers. Aware that I might be going about this the total wrong way...

 

 

0 Kudos
DougBrowning
MVP Esteemed Contributor

Looks like my guess was correct.  You are trying to add together "".  I would remove the relevant from lines 37-47.  It should calc to 0 then your final total will use that 0 instead of "".

Also why are you using '1' and '0' all over.  Change all of these to real numbers.  Adding strings is not going to work out well even when using number() or int() later.  Also this makes you attribute table all wrong.  Change to just 1 and then set the bind type column to int. Or set to integer then set appearance to hidden. That should do it.  I would also bind the final scores to int or better change the type to int then make then read only.

In the end you are trying to add strings is the issue.

Hope that helps

Philine
New Contributor

Thank you so much Doug! That has done the trick. I clearly had no idea what I was doing.

Unfortunately once I hit publish and access the form via a browser I see the dreaded NaN. I have seen other posts about similar issues here on the forum too, but I haven't been able to fix this. Would you be able to advise once more?

Thanks again!

0 Kudos
DougBrowning
MVP Esteemed Contributor

My guess is you missed changing one of the strings.

0 Kudos
Philine
New Contributor

I just don't seem to be able to fix it. Thankful for any advice on what I am missing.

0 Kudos
DougBrowning
MVP Esteemed Contributor

It looks like this is in a really old schema.  Have you not updated 123 in some time?  Next it looks like you did some copy paste vs paste values and messed up all the validation in excel.  Always use paste values in excel.  The bind should be int not integer but your drops downs are all broke.  May be one column off too.  But really on lines 32 to 49 you do not need either bind since you have the field type as integer.  Next why change the Yes and no to 1 and 0?  That will look ugly in the data and years from now no one will know what 1 and 0 are.  I always keep the name and label the same or its just confusing.

There is no need for all those temp fields so I removed all of those.  Your calcs make no sense to me but i assume they are right.  I did all but 3 of them so fix that up.  I also moved you to the new schema.  It all works now.

Hope this helps these are all valuable lessons.  

 

0 Kudos