Count empty rows using aggregation

[creepingdeath]

I want to count rows where Technique_Detection is empty group by Numero_Sillon, Type_Horaire Technique_Detection

This was the expression I used :

count(Aggr(count( {<Technique_Detection={'GNSS','Localisateur','ObservationManuelle'}>} Technique_Detection),Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire))

and this is the unexpected result that I want to avoid.

Jalons Couverts : is the number of rows covered by "Technique_Detection" and should be 0 in this case

Total_Jalons : is the total calculated by this expression : count(TOTAL Aggr(count(Ordre),Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire))

Taux_de_couverture: is the coverage pourcentage wich should be 0%

Thanks in advance.

[creepingdeath]

Any help ?

[edwin · Accepted solution]

a simple solution would be to add an EMPTY flag or count in your script:

load ...
if(isnull(Technique_Detection ),1) as BlankTech,
...

this way it will be a simple SUM(BlankTech) to get all blank values for that field instead of creating a complex expression

[edwin · Accepted solution]

if the field is blank and you count it - it will return 0, so in your statement 

Jalons Couverts : is the number of rows covered by "Technique_Detection" and should be 0 in this case

there is something else going on, if you can show your expression for this, it would be helpful.

another way of counting nulls is this:

if you have Product and Type, and you want to count products with null TYpes:

count(Product) -all products
count(Type={"*"}Product) - all prpoducts with NON NULL types
count(Product) - count( Type={"*"}Product) - all products with NULL Types

[edwin]

there will of course be a few other ways to do it and some may apply to your specific need based on your business rules and some may not.