topic Re: How do I get a count for values in a field in QlikView

How do I get a count for values in a field

Anonymous — Tue, 06 Jan 2015 19:29:52 GMT

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.

Re: How do I get a count for values in a field

saurabh5 — Tue, 06 Jan 2015 19:44:39 GMT

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

Re: How do I get a count for values in a field

NickHoff — Tue, 06 Jan 2015 19:58:33 GMT

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

Re: How do I get a count for values in a field

Anonymous — Tue, 06 Jan 2015 20:01:46 GMT

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.

Re: How do I get a count for values in a field

Anonymous — Tue, 06 Jan 2015 20:02:11 GMT

It's only returning the number 1 back.

Re: How do I get a count for values in a field

saurabh5 — Tue, 06 Jan 2015 20:04:50 GMT

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.

Re: How do I get a count for values in a field

NickHoff — Tue, 06 Jan 2015 20:09:52 GMT

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.

Re: How do I get a count for values in a field

Anonymous — Tue, 06 Jan 2015 20:15:39 GMT

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?

Re: How do I get a count for values in a field

saurabh5 — Tue, 06 Jan 2015 20:24:55 GMT

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

Re: How do I get a count for values in a field

Anonymous — Tue, 06 Jan 2015 20:42:56 GMT

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)

Re: How do I get a count for values in a field

NickHoff — Tue, 06 Jan 2015 20:50:49 GMT

=num(count({<clothes={'Shirts'}>}clothes),'#,##0')

You could also go into the number format tab and format the numbers there.