topic Selecting Null Values in Set Analysis in QlikView

Selecting Null Values in Set Analysis

Fri, 20 Nov 2009 21:00:25 GMT

I am trying get the amend the expression below to also select records where Cancelled_Date is null.

Is this possible?

=Sum({<[Cancelled_Date]={">$(=getfieldselections(Period))"}>}[Contract_Fee])

Fri, 20 Nov 2009 21:03:53 GMT

If so, you should be able to use Null() for that. It returns null.

Or IsNULL() which returns a boolean on checking if some expression returns null.

You can use Null() like this: IF ([Cancelled_Date]=Null(), ...

Fri, 20 Nov 2009 21:41:26 GMT

You can get results where Cancelled Date is null by using:

Sum({<[Cancelled_Date]={""}>}[Contract_Fee])

To get both, I think you can use:

Sum({<[Cancelled_Date]={">$(=getfieldselections(Period))", ""}>}[Contract_Fee])

I tried it on a simpler Set Modifier and it worked, but I've never used that in practice.

Sat, 21 Nov 2009 00:19:52 GMT

I have tried a few different ways with no luck so I am using

Sum

Instead

Thanks for your help

({1-$<[Cancelled_Date]={"<$(=getfieldselections(Period))"}>}[Contract_Fee])