Calculating totals from entries in rosters within rosters

Hello,

I have a questionnaire of farmer economic interest groups. I am asking the leaders of these groups how many perimeters are part of their group in a list question. This list question is the base question for a roster on these perimeters (Perimeter Roster). Within this roster, I have yet another list question, asking how many farmer cooperatives are part of each perimeter. This list question is the base question for the second roster (Cooperative roster), which is nested within the Perimeter roster. In this second roster I then ask how many farmers are in each cooperative. I would like to be able to sum the total number of farmers across all of the cooperatives in all of the perimeters, in order to check answers to other questions in the questionnaire, outside of these rosters. Is this possible to do? And if so, what would be the syntax for this calculation?

1 Like

Hey Elena.

Give it a try by creating a variable (double) at the highest level/the level at which you want to sum the total number of farmers. The syntax should be:
perimeter.Sum(x=>x.cooperative.Sum(t=>t.NUMERICQUESTION))

where NUMERICQUESTION== How many farmers are in each cooperative.

Cheers,
Peter

3 Likes

Thank you! This is exactly what I needed.

Best,
-Elena

Nice question–and nice answers!

Building to Peter’s answer…

Here is the structure I think you have

leaderList (list question)
leaderRoster (triggered by leaderList)
	perimiterList (list question)
	perimeterRoster (triggered by perimiterList)
		cooperativeList (list question)
		cooperativeRoster (triggered by cooperativeList)
			numFarmersInCoop (numeric question)

If so, put code of the following form in a variable, or in the validation condition where needed:

// select all perimeters associated with each leader
leaderRoster.SelectMany(y=>y.perimeterRoster).

// select all cooperatives associated with each perimeter
SelectMany(z=>z.cooperativeRoster).

// select numFarmersInCoop from the cooperative roster
Select(x=>x.numFarmersInCoop).

// compute the sum--summing across all leader-perimeter-cooperative observations
Sum()

The SelectMany operator collects all designated nested roster instances. The Select operator selects a variable. Sum, well, sums. From quick testing, it looks like the Sum plays nicely with unanswered questions: that is, computes a sum where NULL values are removed (rather than 1 + NULL = error).

See the support article for roster conditions that look across rows for some external links (here).

Seeing your question makes me realize–and remind me of your former colleague Anca’s request–that the Survey Solutions team build out more examples of advanced syntax. That way, documentation grows with the (impressive) skills of clients.

This is on my personal to-to list for the next 6 months. In the interim–and even thereafter–keep coming the user forum with questions.

Wow thank you Arthur - this is great. I agree with Anca, finding the syntax for these more complicated scenarios, and understanding the little that is out there without having a deeper Computer Programming background has been a challenge in working with Survey Solutions. I am so glad to hear that you will be developing advanced syntax documentation further - we all at Mathematica are looking forward to that!

Hi there, I am in a similar situation to Elena, but I need to go one step further and have the formula only sum at the crop level. Here is how the variable looks in my survey,

The reason for this is that there are multiple plot owners across the household and sometimes multiple crops per plot (ex. Artichokes on half, tomatoes on the other half of the same plot).

To avoid asking too many questions in an already long survey, we need to take the sum of the amount harvested per crop.

The reason for all this is to validate that the amount inputted in a different roster (at the household crop level) for donations or consumption is not larger than the total amount of crops harvested. Ex. They cultivated 10 kilos of tomatoes on Subplot 1.1 and 20 kilos on Sublot 2.1, they therefore cannot have donated 60 kilos of tomatoes.

Currently, the formula is working, but if there are multiple crops, it is (as it should do as written) pulling in one sum across all crops on all the subplots.

Please advise on how to alter this variable or add another one that would allow the validation condition to be tied to each crop. Will provide more screenshots if necessary.