Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Counting occurrences of a filed

I have a table that links to other tables. One of the column in this table is an indicator that can be one of two values. I have an expression on the bar chart to count occurrences(rows) with this value

=count(if([ARM Map Indicator]='L3',1,0))

and

=count(if([ARM Map Indicator]='L4',1,0))

However I am getting the same value for both counters. Any ideas?

Thank you. Yury

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

It cannot work, because it will count 0s as well as 1s. Using sum is relaibale:
sum(if([ARM Map Indicator]='L3',1,0))

View solution in original post

6 Replies
vgutkovsky
Master II
Master II

Well, you're counting 1 Smile If you want to use 1, use it in combination with sum instead of count.

Regards,

Anonymous
Not applicable
Author

It cannot work, because it will count 0s as well as 1s. Using sum is relaibale:
sum(if([ARM Map Indicator]='L3',1,0))

johnw
Champion III
Champion III

And set analysis is likely faster:

count({<[ARM Map Indicator]={'L3'}>} [ARM Map Indicator])

Not applicable
Author

Thank you , sum worked

Not applicable
Author

not sure I understand. sum worked but I also want to understand your suggestion

johnw
Champion III
Champion III

Well, it's just the set analysis version of an IF statement. Basically, QlikView first "selects" the value of L3 for [ARM Map Indicator]. Then it counts how many rows that [ARM Map Indicator] appears on. It's a way to not have to even LOOK at the rows that don't have L3, which is why it is typically faster than using an IF. And in a sense, it doesn't matter which field you actually count at that point. Just about any field in the table (other than a field shared by other tables) would work fine. I typically have a unique ID for each row in each table, so I'd typically count the unique ID instead. But it works either way, and I didn't know if you had a unique ID, or what it might be named.

For more information on set analysis, just go to the index in the help file, and look for "set analysis". There's a whole lot it can do.