set analysis with isnull not working

[ashmitp869]

Hi there,

Please kindly help me with the set analysis expression :

Sum({<Identifier={'T','L','R'} OR  ISNULL(Identifier)>}DISTINCT [Episodes])

The IsNull part is not working , I tried to used  OR Len(Identifier) > {0}>}  and OR Identifier= {"=Len(Identifier) > 0"}>}
but still red mark is coming.

[PiEye · Accepted solution]

Set analysis  can only replicate selections that you make in the front end. Anything that you can type in a list box or search bar, can be used in set analysis.  You'll see that you can't select nulls.

There are two options here.

The ideal solution is to replace the nulls with a value such as "NA", so that you can actually make a selection with them - see an easy way to do this here  .

The value can then be included in set analysis with the others: Sum({<Identifier={'T','L','R','NA'} >}DISTINCT [Episodes])

Other than that you'll have to use some of the set operators to exclude "has a value" and then add back in the ones you want

Sum({-<Identifier={"*" }>+<Identifier={"T" ,"L","R"}>}DISTINCT [Episodes])

Does this work?

[ashmitp869]

I used a id_null_flag in the script like -if(isNull(Identifier),1,0) as id_null_flag

1. Is this if condition

 (IsNull([Identifier]) Or [Identifier]InList("T";"R";"L"))

Similar to qlikview expression

Count({<Identifier={'T','R','L'},id_null_flag ={1}>}DISTINCT [Episodes])

[PiEye]

Hi Ashmit, this expression 

Count({<Identifier={'T','R','L'},id_null_flag ={1}>}DISTINCT [Episodes])

Will be similar to an "AND". Similar to what I wrote above, if you imagine you are making the equivalent filters in the front end, this would mean you filter on identifier, then the null flag meaning you end up with the intersection of them both.

In order to get an "OR" you can either:

• "Add" two set analysis expressions together, like thus: 
  • <Identifier={'T','R','L'}>+<id_null_flag ={1}>
• Include the null value as a replacement in Identifier (my preffered option) and add it to the list of selected identifiers
  • <Identifier={'T','R','L','NA'}> (where NA is the substituted null value)

Pi