How do I get a count for values in a field

[Anonymous]

Hi All,

I am developing an app and ran into a speed bump. I have an field that is "Clothes" and inside the field there is "Shirts, Pants, Shorts, and dresses."

How can I make a number count in a text box for each clothing article, so I can show the number of shirts, pants, etc.?

I know how to make a number count for Clothes but not for values inside of a field. If someone can assist that would be great - thanks.

[saurabh5 · Accepted solution]

you can use an expression like:

=count(distinct {<clothes={'Shirts'}>}clothes).............for shirts

=count(distinct {<clothes={'Dresses'}>}clothes).............for Dresses.

if you have a unique no. to count like item id then replace clothes by item id.

like:

=count(distinct {<clothes={'Shirts'}>}item_id)

Regards

Saurabh

[NickHoff]

A more efficient way would be to add the frequency to your list box.

[Anonymous]

I would do that if need be, I can also make a bar graph and show each one too. I just want to be able to create these text boxes for the visualization I'm going for.

[Anonymous]

It's only returning the number 1 back.

[saurabh5]

do you have an id to make a count on if not....then please remove distinct from the expression it will give you the exact count.

[NickHoff]

If using the text boxes is what your going for then what Saurabh suggested using count with the set of what your looking for, but without the distinct. 

Keep in mind that when using text boxes they tend to off set several pixels between each object with different browsers which could ultimately hurt your visualization.  This is the case if your clients are still using a browser older than i.e. 9.

[Anonymous]

It's interesting you mentioned removing DISTINCT, because when I have distinct it only returns a value of 1. If I remove it, it counts properly. Why is it only returning a value instead of all non-duplicating values?

[saurabh5]

because it is counting the word 'shirt' in the column clothes distinctly, which will always be 1.

[Anonymous]

Thanks a lot,
What if I needed to format the numbers for a value in the thousands.  Where would I put -    '#,##0'  ?

=count(distinct {<clothes={'Shirts'}>}clothes)