counting "-" value

[Ruud_Hogenboom]

Hi All,

To see which kind of airco is used i have to count 3 specific values in 3 different fields. The values in these fields only contain "TRUE" or "-". If no airco is used then the value should be "-" in all 3 fields, like shown below

Somehow i can't make Qlik to count all the instances when this is the case.

I'm using this formula which doesn't seem to work:

=count({<[OP_530 - AIR CONDITIONING] = {'-'}, [OP_534 - AUTOMATIC AIR CONDITIONING] = {'-'}, [OP_8R9 - REFRIGERANT] = {'-'}>} CTY)

Instead of the '-' i also tried null, '0' and ' '  

What am i doing wrong?

Kind regards,

Ruud Hogenboom

[vikasmahajan · Accepted solution]

yes while loading data load data with following command

If(len(trim(OP_530 - AIR CONDITIONING))= 0 or OP_530 - AIR CONDITIONING='NULL' or OP_530 - AIR CONDITIONING='-', 'NULLAC', OP_530 - AIR CONDITIONING) as OP_530 - AIR CONDITIONING

use NULLAC in set analysis to count null AC directly you can not count null

Thanks

vikas

[stevejoyce]

=count({<[OP_530 - AIR CONDITIONING] = {"=len([OP_530 - AIR CONDITIONING] )=0"}, [OP_534 - AUTOMATIC AIR CONDITIONING] = {"=len([OP_534 - AUTOMATIC AIR CONDITIONING] )=0"}, [OP_8R9 - REFRIGERANT] = {"=len([OP_8R9 - REFRIGERANT] )=0"}>} CTY)