14 Replies Latest reply: Oct 24, 2016 12:40 AM by Pooja Singh

# Null Condition in Set Analysis

Hi All,

I have below expression :

Count({\$<Req_state={3}>}Req_number). However I want to add one more condition that is Closed Date = null.

I am using below script but it is not working.

Count({\$<closed_at = {NULL},Req_state={3}>}Req_number)

Thanks,

Sarif

• ###### Re: Null Condition in Set Analysis

Hi

Count({\$<closed_at -= {'*'},Req_state={3}>}Req_number)

Regards

Harish

• ###### Re: Null Condition in Set Analysis

The above wont work. Try something like,

Count({\$<Req_state={3}>}Req_number) - Count({\$<closed_at -= {"len(trim(closed_at))=0"},Req_state={3}>}Req_number)

Regards

Harish

• ###### Re: Null Condition in Set Analysis

Hi Harish,

Both Expressions are not working... please suggest any other approach.

Sarif

• ###### Re: Null Condition in Set Analysis

Have a look at

Excluding values in Set Analysis

Set analysis is depending on your sets (i.e. your data model and data), but maybe try something like this if Req_number is like a unique id field:

Count({\$<Req_number = e( {< closed_at = {"*"} >} ), Req_state={3}>}Req_number)

• ###### Re: Null Condition in Set Analysis

Null values in a certain cannot be selected - not by inclusion, and not by exclusion (AFAIK).

The trick is to select values in another field that are associated with the NULL values in your field.

For example, the following three versions won't work:

closed_at = {NULL},

closed_at -= {'*'},

closed_at = {"=len(trim(closed_at))=0"}

while the following should return the desired:

Count({\$<Req_number -= {"len(trim(closed_at))=0"},Req_state={3}>}Req_number)

In this expression, you are selecting (or rather excluding) Req_Number values that are associated with the NULL values in closed_at.

cheers,

Oleg Troyansky

• ###### Re: Null Condition in Set Analysis

Do we need to create any field called Req_number? If yes then what would be the expression?

Thanks

Sarif

• ###### Re: Null Condition in Set Analysis

Req number is the field you used in your original formula:

Count({\$<closed_at = {NULL},Req_state={3}>}Req_number)

There is no need in creating new fields. The point is that instead of selecting NULL values in the field closed_at, you should be selecting values of another field that is associated with closed_at.

hope it helps,

Oleg Troyansky

• ###### Re: Null Condition in Set Analysis

hi Stefan and Oleg,

Both expression are not working.

Thanks,

Sarif

• ###### Re: Null Condition in Set Analysis

A simply way is to make change in back end.

eg.

if(len(trim(closed_at))=0,1,closed_at) as closed_at

from<table>.

Now in expression:

Count({\$<closed_at = {'1'},Req_state={3}>}Req_number)

Regards

KC

• ###### Re: Null Condition in Set Analysis

requesting you to use following for adding null values of closed_at field.

Count({\$<closed_at = {"=Len(City)=0"},Req_state={3}>}Req_number)

Thanks,

• ###### Re: Null Condition in Set Analysis

just correcting my  above expression,requesting you to use following for adding null values of closed_at field.

Count({\$<closed_at = {"=Len(closed_at)=0"},Req_state={3}>}Req_number)

Thanks,

• ###### Re: Null Condition in Set Analysis

Hi,

have you tried a simple IF statement in the expression?

This would be like this:

Count(

IF(isNull(closed_at) AND Req_state = 3,

Req_number

)

)

• ###### Re: Null Condition in Set Analysis

Hi ,

You can use below expression:-

=count({<Req_number={"=isnull(closed_at)"}>} distinct Req_number)

Regards,

Udit