How can I formulate my count function.

[Anonymous]

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?

[v_iyyappan]

Hi,

Null Count in Sub Area:

Try like this :

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

Regards,

[Anonymous]

Unfortunately this did not work   

[vikasmahajan]

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

[tresesco]

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])

[sujeetsingh]

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