topic Re: Null Values in QlikView

Null Values

Wed, 01 Aug 2012 18:46:13 GMT

I want my expression to calculate the sum of 2012 'RevDollars' and include the following conditions:

StartDate = Prior to 2008 (oldest date in records is 1984)
Include Null and Not-Null Values for StartDate (oldest record is 1984, but lots of null values are present in data set)

This is what I have so far, but I don't know how to include the null values:

sum({$<Year={2012},StartDate={'=<2008'}>}RevDollars)

Can anyone help me fix the syntax?

Re: Null Values

chematos — Wed, 01 Aug 2012 18:56:09 GMT

Try this and let me know:

sum({$<Year={2012},StartDate={'<=$(=2008)'},StartDate={'=null()'}>}RevDollars)

try StartDate={'=null()'} or StartDate= {''}

hope this helps

Re: Null Values

Wed, 01 Aug 2012 19:09:15 GMT

I couldn't get it to work. When I try the following two expressions, I only get the non-null values:

sum({$<Year={2012},StartDate={'<=$(=2008)'}>}RevDollars)+sum({$<Year={2012},StartDate={''}>}RevDollars)

sum({$<Year={2012},StartDate={'<=$(=2008)'}>}RevDollars)+sum({$<Year={2012},StartDate={'=null()'}>}RevDollars)

Re: Null Values

chematos — Wed, 01 Aug 2012 19:22:31 GMT

May be null values are discarded because of the filters or the model so it will never add values for null data.

Could you upload your qvw ??

Re: Null Values

Wed, 01 Aug 2012 22:15:24 GMT

Here is an example. Thank you for your help on this!

Re: Null Values

Sokkorn — Thu, 02 Aug 2012 01:24:16 GMT

Hi,

Let try

=sum({$<Year={2012},StartDate={'<=$('2008')'}>} RevDollars) + sum({$-<StartDate=-{}>} RevDollars)

instead of

=sum({$<Year={2012},StartDate={'<=$('2008')'}>}RevDollars)+sum({$<Year={2012},StartDate={''}>}RevDollars)

Regards,

Sokkorn

Re: Null Values

Oleg_Troyansky — Thu, 02 Aug 2012 02:18:41 GMT

I think the correct syntax should be:

sum({$<Year={2012},StartDate={'<=2008', "=len(StartDate)=0"}>}RevDollars)

Re: Null Values

Thu, 02 Aug 2012 13:23:00 GMT

I couldn't get those expressions to work (the nulls were still missing), so I ended up just adding a StartYearIndicator field in the script. I guess it's the long way around, but it worked. Thanks for your help everyone.