Select to view content in your preferred language

How to use operators inside of aggregate functions? [i.e count(${size}<25)]

471
3
Jump to solution
03-11-2024 12:27 PM
PBCartoTech
Emerging Contributor

I'd like to show a count from a repeat collection group of tree diameter values. As surveyors are entering the diameter values into the repeat group, a box will show the count of tree diameters above 25 diameter, and a separate box will show the count of tree diameters below 25 diameter. I would like to do the same with sum, and get a sum of all tree diameters above 25 diameter, and below 25 diameter. 

In the calculation attribute I tried count(${size}<25), but I get an error that says 'type mismatch'

I also tried if(${dbh}<25,count(${dbh}),0), with no error, but the result is always 0. 

Is this possible? If so, how? If it isn't I am open to ideas. My back up idea is to have the surveyor enter tree diameters into two separate repeats, a repeat for above 25 diameter and under 25 diameter. 

0 Kudos
1 Solution

Accepted Solutions
abureaux
MVP Frequent Contributor

In the repeat, I'd have two calculates. One for diameters below threshold (e.g., ${dbh_lt25}), and one for above (e.g., ${dbh_gt25}).  Then outside the repeat, use sum() on the two separate calculates. E.g., sum(${dbh_lt25}) and sum(${dbh_gt25}) 

For the list, do you mean a literal list? In that case, you will want two more calculates in the repeat. One for less than threshold and one for greater than threshold. Both will have a similar format: concat(${dbh_lt25},'<br>') and concat(${dbh_gt25},'<br>'). Then, outside the repeat, you just sum() those up and you will have a list.

Note: When using SUM() like this to create a list, be sure to either set the fields to null or greatly increase the field length. If you don't, the fields will default to 255 character length, and exceeding that will return an annoyingly vague error.

View solution in original post

0 Kudos
3 Replies
abureaux
MVP Frequent Contributor

In the repeat, I'd have two calculates. One for diameters below threshold (e.g., ${dbh_lt25}), and one for above (e.g., ${dbh_gt25}).  Then outside the repeat, use sum() on the two separate calculates. E.g., sum(${dbh_lt25}) and sum(${dbh_gt25}) 

For the list, do you mean a literal list? In that case, you will want two more calculates in the repeat. One for less than threshold and one for greater than threshold. Both will have a similar format: concat(${dbh_lt25},'<br>') and concat(${dbh_gt25},'<br>'). Then, outside the repeat, you just sum() those up and you will have a list.

Note: When using SUM() like this to create a list, be sure to either set the fields to null or greatly increase the field length. If you don't, the fields will default to 255 character length, and exceeding that will return an annoyingly vague error.

0 Kudos
PBCartoTech
Emerging Contributor

Maybe I am misreading, but you are saying the surveyor would need to separate entries above and below threshold into two separate fields?

Ran into some bumps trying to get concat to work, error 'couldn't understand the expression starting at this point...' I put concat(${dbh_lt25},'<br>') in the calculation column

 

I think I partially figured it out, by using hidden integer columns instead of calculate columns. The calculate seemed to be a string instead of number. 

0 Kudos
abureaux
MVP Frequent Contributor

Do you have ${dbh_lt25} as a field within your survey?

I copy-pasted that syntax from a survey that I am doing this same operation in.

Check this out:
abureaux_0-1710356113971.png

In this survey, I have several calculates set up like this to build out a "summary" list. The syntax should be correct.

This is the resulting table:
abureaux_1-1710356286238.png

 

0 Kudos