Count Empty Fields

[Daniel1908]

Hello Together, 

I am using the below expression. 
Unfortunately the count for the empty fields (Address & Address_2) does not work.

[Address]-={'*'}
[Address_2]-={'*'}

Do you have any ideas? 

=Count(aggr(only(distinct{<[Product]={'1'},[Address]-={'*'},
[Product_Text]={"=WildMatch(Left(Product_Text,20),'*New*')"}>}Product_Number)
& only(distinct
{<[Product]={'1'},[Address_2]-={'*'},[Product_Text]={"=WildMatch(Left([Product_Text],20),'*New*')"}>}Product),Product,Options))

Kind Regards, 
Daniel

[MarcoWedel]

{
  $<[Product]={'1'},[Product_Text]={"=WildMatch(Left([Product_Text],20),'*New*')"}>
  -
  $<[Address_2]={"*"}>
}

[Daniel1908]

The expression shows ok but does not count the empty field for Adress and Adress_2.

Any further ideas?

Thanks for your help

Kind Regards, 
Daniel

[marcus_sommer · Accepted solution]

I assume that the values aren't empty else NULL. NULL isn't be stored in any way could be therefore not selected and also not be excluded. AFAIK there are ways to calculate them with an indirect set analysis but it's not always trivial.

Easier is it in many cases to replace the NULL within the script with real values with any of the NULL variables or with measures like:

if(isnull(Field), 'NULL', Field) as Field

or 

if(len(trim(Field)), Field, 'NULL') as Field

which makes these values accessible. You may apply here any string you like maybe also ' ' and should such empty/default value be disturbing you may double the field and using one within the UI and the other only for the set analysis.

- Marcus

[Daniel1908]

Thanks Marcus, 

This worked perfectly. 

Kind Regards, 
Daniel