Counting occurrences of a filed

[#1]

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

[vgutkovsky]

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

Regards,

[Anonymous · Accepted solution]

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]

And set analysis is likely faster:

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

[#5]

Thank you , sum worked

[#6]

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

[johnw]

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.