Set analysis for dates (one null,the other not, simultaneously)

[ioannaiogr]

Hi all.

So I was trying to count this scenario: For ID I want to have Date1 null and Date2 not null. I did

count({<Date1={"=(len(Date1)=0)"},Date2={"=(len(Date2)>0)"}>} distinct ID)

but it is not working because in my straight table I can check that there's >10 with such conditions.

Help! Please 🙂

Thank you in advance! 

[sandeep-singh]

Try this

count({$<Date1={"=(len(Date1)=0)"},Date2-={"=(len(Date2)=0)"}>} distinct ID)

or

count({$<Date1={"=(len(Date1)=0)"},Date2-={"NULL"}>} distinct ID)

[marcus_sommer]

NULL doesn't exists and couldn't be selected, displayed or counted - at least not directly. The easiest way to an access to this information is usually to replace the NULL with real values. In your case it might be something like:

if(len(trim(Field)), Field, '<NULL>')

You may apply each kind of string you want - maybe just ' ' - or by date-fields replacing them with today() or the year 9999 or similar. Further you may do it within an extra field if your origin field-values should be untouched.

[ioannaiogr · Accepted solution]

First of all,

thank you all for putting time and effort to help me.

I found my solution in @marcus_sommer 's reply. What a brilliant and smart way to handle this type of data. Thanks! It all works now.