Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
Null Count in Sub Area:
Try like this :
=COUNT({$ <Flag={1},Area ={'Foreign Body'},Sub_Area ={''}>}Distinct [SR_Ref])
Regards,
Unfortunately this did not work
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
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])
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