# How to use advanced calculation field

Hello house, I want a situation whereby if I choose Category A and in the next question I choose score 0, then the next question which is risk analysis should be “N.A.R”

Also if I Choose Category A and in the next question I choose score 1, then the risk analysis should be “L.A.P”
it should be in the order of the excel snapshot

You’ll need to constructed a suitable nested-if statement to cover all the permutations for risk analysis; eg

`if(\${category}='A' and \${score}=0, "NAR", if(\${category}='A' and (\${score}=1 or \${score}=2), "LAP", if(...)))`

basically, each subsequent risk analysis condition goes in the else bit of the previous `if`, till you run thru all the permuations [I’d suggest making the final else cover “UA” so you dont have to enumerate all its conditions]. ie

`if(<nar_check>, "NAR", if(<lap_check>, "LAP, if(<hap_check>, "HAP", if(... if(... , "UA")))))`

Hopefully that’s enough to point you in the right direction…

2 Likes

Thank you very much

np. let us know how you get on

Hello,
As idea: Another solution might be to set the risk value as name (or label) using a select_one (read_only) with multiple choice_filters (category, score).

Furthermore the example (see post 1) would allow to condense the nested-if solution by using score ranges resulting in the same risk value.

This is what I was able to do .

I make the category question to be mandatory with option category A, category B …E,
then the score to be number, between 0 and 9
after that I calculated field with skip logic, then, I added a note in the final result and it works.

The only problem is if i go back it and select another category, it would give multiple answer.

Kindly check the picture above.

I will also try you logic.
also check how I perform the logic.

an68HvRP6Tu2YmCsf5R2XL.xlsx (9.2 KB)

Have a look at the nested-if advice from Xiphware. It is ONE long if, not several as you did now. This results in (calculated) ONE variable.
Also, your last to calculation are doing the same?
Better stay with the nested-if solution, the other idea might be more difficult to implement for you…

THANK YOU, , I followed Xiphware method and it works.
I had to write the code in an I.D.E before transferring it to kobotoolbox.

this is the code

if(\${Q9}=1 and \${Q10}=0, “N.A.R”, if(\${Q9}=1 and \${Q10}=1, “L.A.P”, if(\${Q9}=1 and \${Q10}=2, “L.A.P”, if(\${Q9}=1 and \${Q10}=3, “L.A.P”, if(\${Q9}=1 and \${Q10}=4, “H.A.P”,if(\${Q9}=1 and \${Q10}=5, “H.A.P”,if(\${Q9}=1 and \${Q10}=6, “H.A.P”, if(\${Q9}=1 and \${Q10}=7, “U.A”, if(\${Q9}=1 and \${Q10}=8, “U.A”, if(\${Q9}=1 and \${Q10}=9, “U.A”, if(\${Q9}=2 and \${Q10}=0, “L.A.P”, if(\${Q9}=2 and \${Q10}=1, “L.A.P”, if(\${Q9}=2 and \${Q10}=2, “L.A.P”, if(\${Q9}=2 and \${Q10}=3, “L.A.P”, if(\${Q9}=2 and \${Q10}=4, “H.A.P”, if(\${Q9}=2 and \${Q10}=5, “H.A.P”, if(\${Q9}=2 and \${Q10}=6, “H.A.P”, if(\${Q9}=2 and \${Q10}=7, “U.A”, if(\${Q9}=2 and \${Q10}=8, “U.A”, if(\${Q9}=2 and \${Q10}=9, “U.A”, if(\${Q9}=3 and \${Q10}=0, “H.A.P”, if(\${Q9}=3 and \${Q10}=1, “H.A.P”, if(\${Q9}=3 and \${Q10}=2, “H.A.P”, if(\${Q9}=3 and \${Q10}=3, “H.A.P”, if(\${Q9}=3 and \${Q10}=4, “H.A.P”, if(\${Q9}=3 and \${Q10}=5, “H.A.P”, if(\${Q9}=3 and \${Q10}=6, “H.A.P”, if(\${Q9}=3 and \${Q10}=7, “U.A”, if(\${Q9}=3 and \${Q10}=8, “U.A”, if(\${Q9}=3 and \${Q10}=9, “U.A”, if(\${Q9}=4 and \${Q10}=0, “U.A”, if(\${Q9}=4 and \${Q10}=1, “U.A”, if(\${Q9}=4 and \${Q10}=2, “U.A”, if(\${Q9}=4 and \${Q10}=3, “U.A”, if(\${Q9}=4 and \${Q10}=4, “U.A”, if(\${Q9}=4 and \${Q10}=5, “U.A”, if(\${Q9}=4 and \${Q10}=6, “U.A”, if(\${Q9}=4 and \${Q10}=7, “U.A”, if(\${Q9}=4 and \${Q10}=8, “U.A”, if(\${Q9}=4 and \${Q10}=9, “U.A”, if(\${Q9}=5 and \${Q10}=0, “U.A”, if(\${Q9}=5 and \${Q10}=1, “U.A”, if(\${Q9}=5 and \${Q10}=2, “U.A”, if(\${Q9}=5 and \${Q10}=3, “U.A”, if(\${Q9}=5 and \${Q10}=4, “U.A”, if(\${Q9}=5 and \${Q10}=5, “U.A”, if(\${Q9}=5 and \${Q10}=6, “U.A”, if(\${Q9}=5 and \${Q10}=7, “U.A”, if(\${Q9}=5 and \${Q10}=8, “U.A”, if(\${Q9}=5 and \${Q10}=9, “U.A”, “N.A.R”))))))))))))))))))))))))))))))))))))))))))))))))))

thanks to Xiphware and wroos

1 Like

Well done! (note, I didnt say it would be ‘pretty’… lol )

Note, you can probably simplify everything somewhat by looking for commonalities and condensing things; eg the above sub-expression is logically equivalent to:

`if(\${Q9}=1 and \${Q10}>=1 and \${Q10}<=3, “L.A.P”, ...`

1 Like