topic Re: What is the syntax to exclude null date fields from a count expression? in QlikView

What is the syntax to exclude null date fields from a count expression?

mgavidia — Thu, 31 Jan 2013 01:12:45 GMT

I am having trouble with Set Analysis syntax to come up with a total count of records.

(I am using a counter so the function is SUM instead of count)

I need to get the total count of records for all' Rented' units where the MoveOutDate is null. (sounds easier that it is).

I thought it was as simple as my statement below but it is not.

Sum({<UnitStatus={'Rented'},isnull(MoveOutDate)>}#MoveInCounter)

I have tried some other complex suggestions I found in this forum but so far nothing has worked for me.

Thanks in advance.

Miguel Gavidia

Thu, 31 Jan 2013 01:18:55 GMT

Hi.

Maybe not the faster way, but just to see if it works.

Sum(if(UnitStatus='Rented' and isnull(MoveOutDate),MoveInCounter))

Alessandro Furtado

mgavidia — Thu, 31 Jan 2013 03:28:12 GMT

Alessandro, Your above solution resolved my problem; now I can move on to the next hurdle on my project. Thank you very much! Regards, Miguel Gavidia

rwunderlich — Thu, 31 Jan 2013 04:39:04 GMT

An excellent write up on general processing of nulls can be found here

Recommended reading for all QV Developers

-Rob