topic Re: Re: How can I formulate my count function. in QlikView

Re: How can I formulate my count function.

Anonymous — Wed, 11 Sep 2013 13:24:29 GMT

The complaints reporting application in which am trying to count the different complaints by Area and Sub Area

below is my current expression which is working ok, apart from when I total all sub Areas counts they do not equal the total of the Area count.

COUNT({$ <Flag={1},Area ={'Foreign Body'},
Sub_Area ={'Other',Physical','Amphibian','Animal','Conglom','Meat','Shell','Material','Rubber','Unidentified'}>}Distinct [SR_Ref])

When I checked out the data, I can see this is because there are some complaint lines that have i.e .

Area =Foreign Body

Sub Area = has no value.

How can I count the Null values aswell?

Re: How can I formulate my count function.

v_iyyappan — Wed, 11 Sep 2013 13:37:32 GMT

Hi,

Null Count in Sub Area:

Try like this :

=COUNT({$ <Flag={1},Area ={'Foreign Body'},Sub_Area ={''}>}Distinct [SR_Ref])

Regards,

Re: How can I formulate my count function.

Anonymous — Thu, 12 Sep 2013 08:38:24 GMT

Unfortunately this did not work

Re: How can I formulate my count function.

vikasmahajan — Thu, 12 Sep 2013 08:54:06 GMT

while Loading Data

create filed if(ISNULL(Sub_Area) OR LEN(Sub_Area)=0,1,0)) AS as Sub_Area

Use Expression

=COUNT({$ <Flag={1},Area ={'Foreign Body'},Sub_Area ={'0'}>}Distinct [SR_Ref])

This will works...

Vikas

Re: Re: How can I formulate my count function.

tresB — Thu, 12 Sep 2013 08:58:09 GMT

If you want to count it separately, you can try like:

Count(If(Len(Trim(SR_Ref))=0, SR_Ref)

Or, if you are bothered about the total mismatch, you can exclude the null values using AND opearator in set like:

COUNT({$ <Flag={1},Area ={'Foreign Body'}*
Sub_Area ={'Other',Physical','Amphibian','Animal','Conglom','Meat','Shell','Material','Rubber','Unidentified'}>}Distinct [SR_Ref])

Re: How can I formulate my count function.

sujeetsingh — Thu, 12 Sep 2013 09:31:57 GMT

COUNT({$ <Flag={1},Area ={'Foreign Body'},
Sub_Area ={'Other',Physical','Amphibian','Animal','Conglom','Meat','Shell','Material','Rubber','Unidentified'},{Sub_Area =null()}>}Distinct[SR_Ref])

May be this work