Summing and calculating weighted scores from repeat groups (household roster)

I seem to have an error in the one or more calculations, and I am unsure as to how to fix it, I believe the error is in either scoring the age of each household member (if they are 5 and under, or 65 and older) or in calculating the cummulative disabilities for each household member. Otherwise stated if household member A has 1 disability and the are aged 69 a score of 2 should be returned. If household member B is under 5 and has 2 disabilities a score of 3 should be returned for that individual. From these two members of the household, 5 points should be added to Tier 1 score. I am asking assistance to check all calculations as, I am confident that there is an error in one of the calculations. I have also attached the matrix so you can see how the points should be allotted.Scoringmiscalculation.xlsx (28.0 KB)

Copy of Income Support Eligibility and Weighting Matrix (tom)5.7.2020VULNERABILITIES.xlsx (20.7 KB)

Also how do I upload the xls file to the current project and not lose the data in form builder, and without creating a new project. I have tried to import previously(maybe incorrectly) and have lost information on the xls, or is unable to see the inputs in form builder.

1 Like

did I miss a reply to this post?

Hi @scherrie,

You could do the same as outlined in the images shown below:

In the survey tab of your xlsform:

In the choices tab of your xlsform:
Image 2

Data entry screen as seen in Enketo:

Reference xlsform:
Disability Cumulative Score for HH.xlsx (11.7 KB)

Have a great day!

Hi Kal,

I have tried my best to understand what is to be done, but I am still getting an xpath error in relation to a calculation. the error reads “There has been a problem trying to replace ${calculation__029} with the XPath to the survey element named"**‘calculation__029’. There is no survey element with this name.”

Error is scoring Calculation.xlsx (34.4 KB)

Additionally, I do not want each instance of disability to be scored. For example If eac

Member A is <=5 and has 1 disability= score 1 for age and 1 for disability
Member B is <=5 and has >=2 disabilities= score 1 for age and 2 for disabilities

Member C is >=66 has 3 disabilities= score 1 for age and 2 for disabilities
Member D is 18 and has no disabilities= score )

Cummulative weighted score for household members should be 3(age) + (5) disabilities = 8 This is what I need to write the calculation for

Hi @scherrie,

I have revised my previous xlsform. It should now work as per your requirement.

In the survey tab of your xlsform:

In the choices tab of your xlsform:
Image 2

Data entry screen as seen in Enketo:

Reference xlsform:
Disability Cumulative Score for HH (Modified_2020 05 11).xlsx (11.7 KB)

Have a great day!

The calculation seems to be incorrect still.

Based on the images you sent it should be as follows:

Cummulative number of eligible HH members with some sort of disability =1
Cummulative number of disabilities within the HH members =2 (eventhough no point is scored for age for the 18 year old, he has a disability so that disability should score 1)
Total weighted score for the household should be = 4 [ (age (5)

  • 1 disability+2 disabilities)]

So just to underscore, even if a HH member is aged 6-64 a point will not be calculated on age, but if the have 1 disability (1 point is scored) and if they have 2 or more disabilities (2 points should be scored)

Can you take a look again please.

1 Like

Hi @scherrie,

Sorry i misunderstood your requirement earlier. I now got you clear. So you require the following calculations:

A query from my side:

What if Member E is 20 years and has 2 disabilities?

Have a great day!

If member is 20 years and has 2 disabilities he should not be scored on age, but should get 2 points for having (2 or more disabilities)
If he was 20 and had 1 disability he should not be scored on age, but scored 1 point for having one disability.

Can you help me please.

Hi @scherrie,

And what if Member F is <=5 or >=65 bus has no disability?

Have a great day!

They will not be scored. So a value of zero for age and zero for no disability for Member F in such instances.

So a calculation also has to be done for that.

Hi @scherrie,

So here is finally what you wish for:

In the survey tab of your xlsform:

In the choices tab of your xlsform:
Image 2

Data entry screen as seen in Enketo:

Reference xlsform:
Disability Cumulative Score for HH (Modified_2020 05 11).xlsx (11.6 KB)

N.B. Though the current workout fulfilled your query, please have a look at the solutions provided above. It should give you different ideas on calculations.

Have a great day!

1 Like

hi Kal which calculation do i put in the tier 1 score?

Hi Kal I am having a problem wehre the cummulative number of disabilities is calculating incorrectly.

See example below which shows what is the output:

Member A age 2 with 1 disability
Member B age 68 with 3 disabilities

  1. Cummulative house hold members with disabiliites =2
  2. Cummulative number of disabilities within the household =3
  3. Total weighted score =5

Points 1 and 3 are calculating correctly. However, the cummulative disabilities is incorrect. Should that not be 4 instead of 3? However I notice that some one of the calculations is causing too many points to be scored. I need you to look at that and tell me which are not calculating correctly and which calculation should be removed from the Tier 1 scoring.

See rows 101-105; 120-122; and rows 201.
Appreciate your help. Getting closer to resolvind this.

BVI Kal.xlsx (34.4 KB)

I am sorry to be a bother. I have looked at Kal’s previous reply and replicated it in a shortened form where the calculations worked. I then tried to place the same calculatations in the active file, but cannot get it to calculate and produce the variables which it should. I have highlighted the sections to focus on, as well as the Tier 1 score which should be the cummulate score of all weighted scores. Something is off, and I have spent the entire afternoon trying to figure out what is wrong. I would really appreciate if my form is looked at to see wehre the error(s) in the calculations are occuring.

Hi @scherrie,

So here is the other variable that you have been requesting for:

You could do the following as outlined in the image below:

In the survey tab of your xlsform:

In the choices tab of your xlsform:
Image 2

Data entry screen as seen in Enketo:

Reference xlsform:
Disability Cumulative Score for HH (Modified_2020 05 12).xlsx (11.7 KB)

Have a great day!

1 Like

Hi @scherrie,

Regarding your query on …

It depends based on your study requirement. However, if you ask me i would prefer the value from the box highlighted in red below Total weighted score for the household: as it captures a composite score of both the values that has been calculated viz.

  • Cumulative number of eligible HH members with some sort of disability:
  • Cumulative number of disabilities within the household:

But always better to consult with your study team who would best be able to outline the study requirements.

Have a great day!

So Just for clarity the total weighted score would be as in your example “C2”. But when I add it to Tier 1 score should I write it as coalesce(${calcuation_C2},0)?

Hi @scherrie,

Simply use ${C2}.

Have a great day!