Advanced Calculate Function

Hi All,

Currently trying to input a calculate function on my form

Calculate function is
=IF(${How_many_days_in_the_past_one__004}<1,1,IF(${How_many_days_in_the_past_one__004}>=1,2,IF(${How_many_days_in_the_past_one__004}<=6,2,IF(${How_many_days_in_the_past_one__004}>6,3))))

It works on excel, but not when deploying the form to the server. Attempting to change values for another calculate function

When attempting to delpoy, kobo says this
your form cannot be deployed because it contains errors:

Unexpected KoBoCAT error 400: {“detail”:“ODK Validate Errors:\n>> XForm is invalid. See above for the errors.\norg.javarosa.xform.parse.XFormParseException: Invalid calculate for the bind attached to “${dairy_vit_A_code}” : Bad node: org.javarosa.xpath.parser.ast.ASTNodeAbstractExpr@7a0ac6e3 in expression =IF( ${How_many_days_in_the_past_one__004} <1,1,IF( ${How_many_days_in_the_past_one__004} >=1,2,IF( ${How_many_days_in_the_past_one__004} <=6,2,IF( ${How_many_days_in_the_past_one__004} >6,3))))\n\nResult: Invalid”}

Any tips for this one?

Thanks,
Ben

Hi, you have several errors in your code:

  1. try to use small letters if instead of IF
    if((${How_many_days_in_the_past_one__004}...
  2. Your current code will evaluated until the second if() as it blocks the rest
    ...if(${How_many_days_in_the_past_one__004}>=1...
  3. Your last if() is not finished as it need the third argument
    ...if(${How_many_days_in_the_past_one__004}>6, 3, [another_value])...
    Hope that helps

Hi @Odil

Thanks for getting back to me,

Point 1 - Wow, didn't know that so thankyou, so simple haha

Point 2 - I changed the code to =IF(X=0,1,IF(X=1,2,IF(X=2,2,IF(X=3,2,IF(X=4,2,IF(X=4,2,IF(X=5,2,IF(X=6,2,IF(X=7,3)))))))))
This seems like quite a tedious and long one, but I think I got rid of the boundary issues you mentioned in point 2

Point 3 - Sorry unsure about what you mean by this point. why the [another_value]

Thanks again for your help

Conditional operator if has 3 arguments:
if(x=1, yes, no)
the above code reads as: if x=1 return yes, else return no
x=1 <= the first argument;
yes <= the second argument;
no <= the third argument.
in your last if you didn't have the third argument. IF(X=7,3)

Hope that helps,
Odil

Actually you can use the following code:
if(${How_many_days_in_the_past_one__004}=0, 1, if(${How_many_days_in_the_past_one__004}=7, 3, 2))
But note that "How_many_days_in_the_past_one__004" should not return the value bigger than 7.

@Odil

Thankyou so much for clearing that up! Worked a treat and really appreciate it

Thanks,
Ben

Hello @benmcdermott,

If not mistaken you are building some food security kind of questionnaire... if so, please see this link for a form you can tailor to your context. It has most of the calculations you are looking for including but not limited to Food Consumption Score (FCS), FCG, FCS-N (Vitamins, Proteins and Hem-Iron).

The standards used are the latest from WFP, but you tweak to fit you needs.

Hoping it saves you some of the coding and analysis time

Cheers,
Jules R

2 Likes

Hi @Odil and @jules_rugwiro

Thankyou for all the support with doing calculations.

I have finalised the Nutritional indicators which is great, but now been asked to imbed household hunger scales into system.

This relies on two different variables so using the 'if' and the 'and' function, would you guys see this as correct?

My formula is
if(and(${During_the_last_completed_4_we="yes", ${If_Yes_How_often_did_this_happen}=rarely__1_2_ti, 1), if(and(${During_the_last_completed_4_we="yes", ${If_Yes_How_often_did_this_happen}=sometimes__3_1, 1), if(and(${During_the_last_completed_4_we="yes", ${If_Yes_How_often_did_this_happen}=often__10__tim, 2, 0)))

Annoyingly, when i deploy the form it states
"There has been a problem trying to replace ${During_the_last_completed_4_we="yes", ${If_Yes_How_often_did_this_happen} with the XPath to the survey element named 'During_the_last_completed_4_we="yes", ${If_Yes_How_often_did_this_happen'. There is no survey element with this name"

I have checked the variables and they are correct, do you guys have any tips for this formula or previously developed calculations/formulas?

Thank you in advance,
Ben

Quick look fix:
You are missing closing curly brackets here:
${During_the_last_completed_4_we="yes" should be ${During_the_last_completed_4_we}="yes"

Hope this helps,
Odil

Hi @Odil,

I can't believe I missed that, sorry, been staring at the formula for too long I guess. It did solve the first issue, now Kobo is saying I am missing a 3rd argument. I thought my 0 at the end, provided the 3rd argument?

Thanks,
Ben

How about this:
if(${During_the_last_completed_4_we}="yes", if(${If_Yes_How_often_did_this_happen}=often__10__tim, 2, 1), 0)
I'm curious why you have yes quoted and often__10__tim not

Hi @Odil,

That did work to classify a no=0 for the following question.
However, this doesn't separate the 3 answers of rarely, sometimes and often to 2 groups of values = 1 or 2. It classifies all answers of If_Yes_often_did_this_happen = 2. Where I need answers of rarely or often = 1.
I have quoted yes because that's the answer from the label column in choices sheet. often_10_tim is from the name column

Thanks,
Ben

Hi @benmcdermott, did you solve this issue?

Hi @Odil,

Sadly I have not been able to,

I tried a few other formulas, but I don't think I am as good as you with these

These are my attempts to re-code the answers into 3 numerical values,

if(${During_the_last_completed_4_we}="no", 0, if(${If_Yes_How_often_did_this_happen}=often__10__tim, 2, 1))

if(${If_Yes_How_often_did_this_happen_001}=often__10__tim, 2,
if(${During_the_last_completed_4_we_001}="no", 0, 1))

if(${During_the_last_completed_4_we}="no", if(${If_Yes_How_often_did_this_happen}=often__10__tim, 0, 2), 1)

if(${During_the_last_completed_4_we}="no", if(${If_Yes_How_often_did_this_happen} != often__10__tim, 0, 1), 2)

if(${If_Yes_How_often_did_this_happen}=rarely__1_2_ti, 1, if(${If_Yes_How_often_did_this_happen}=sometimes__3_1, 1, if(${If_Yes_How_often_did_this_happen}=often__10__tim, 2, 0)))

if(and(${During_the_last_completed_4_we}="yes", ${If_Yes_How_often_did_this_happen}=rarely__1_2_ti, 1), if(and(${During_the_last_completed_4_we}="yes", ${If_Yes_How_often_did_this_happen}=sometimes__3_1, 1), if(and(${During_the_last_completed_4_we}="yes", ${If_Yes_How_often_did_this_happen}=often__10__tim, 2, 0))))

if(${During_the_last_completed_4_we_001}=yes, 0, if(${If_Yes_How_often_did_this_happen_001}=rarely__1_2_ti, 1, if(${If_Yes_How_often_did_this_happen_001}=sometimes__3_1, 1, 2)))

if(${During_the_last_completed_4_we_002}=yes, 0, if(${If_Yes_How_often_did_this_happen_002}=rarely__1_2_ti, 1, if(${If_Yes_How_often_did_this_happen_002}=sometimes__3_1, 1, 2)))

if(${During_the_last_completed_4_we}="yes", if(${If_Yes_How_often_did_this_happen}="Often (10+ times)", 2, 1), 0)

Thanks again for your help,

Could you share your excel file? At least that part, so I could look at it?

PDM Trial Version 1.xls (118 KB)

Hi Odil,

I have uploaded my excel, the calculation codes are right at the bottom.

Thankyou,
Ben

Try this:
if(${During_the_last_completed_4_we}="no", 0, if(${If_Yes_How_often_did_this_happen}=often__10__tim, 2, 1))

This will code 0 if question:
"During the last completed 4 weeks of Food Card use, was there ever no food to eat of any kind in your house because of lack of resources to get food?" = no;
but if it is = yes, it will code 2 if answer to the question "If Yes, How often did this happen?" = Often (10+ times);
and for the rest it will code 1

Hope this helps,
Odil

Hi @Odil,

I tried this type of equation and it doesn't separate the answers of rarely, sometimes and often correctly. Codes them all as 1 sadly

Sorry to be a hassle,
Ben

This is the working formula:
if(${During_the_last_completed_4_we}="no", 0, if(${If_Yes_How_often_did_this_happen}="often__10__tim", 2, 1))
the answer options should be quoted "often__10__tim"
PDM_Trial_Version.xml (9.8 KB) PDM_Trial_Version.xls (42.5 KB)

As I wrote above:

Hope this helps with your further designs.
Odil

Hi @Odil,

Sorry for missing that, didn't know choices had to be quoted if they had the coded variable in,

Thanks for all the help again!
Ben