Help with NullCount combined with If Function

[#1]

Hello Qlikview Community,

I would very much appreciate help with the following quandry.

I am trying to count null values in a field, with the added caveat of an If statement in another field.

To give you an idea of the data:

CCR Reference	Date Customer Approved SoW	POB_Date	Chargeable	Budget Code	Change Control Ref
56644	41162	19/09/2012	Yes	NO259	CC975
59893	41150	19/09/2012	Yes	PN190 1906	CC973
59895	41117	-	Yes	-	-
59914	41212	21/11/2012	Yes	ET100 2600  ST001	-
59923	41106	-	Yes	66236	-
59931	41128	19/09/2012	Yes	ET100 2600 ST007	-
59971	41156	19/09/2012	Yes	CA051 1400	-
60413	41152	21/11/2012	Yes	PL012	-
60415	41134	19/09/2012	Yes	SB013 1903	-
60498	41159	19/10/2012	Yes	HR413 2104	-
62085	41159	19/10/2012	Yes	HR413	-

I would like to count the records where the 'Chargeable' field is Yes, but the 'Change Control Ref' is null, or blank.

I have tried various iterations, including the following, none of which work:

=count(if(Chargeable = 'Yes' and [Change Control Ref] > 0, CCR Reference))

=NullCount(distinct if(Chargeable = 'Yes', [Change Control Ref]))

Any ideas?

Many thanks,

Frances

[pover · Accepted solution]

How about the following?

=count(if(Chargeable = 'Yes' and len([Change Control Ref]) = 0, CCR Reference))

You could use the insull() function, too, but I prefer the len() function since it treats empty cells as if they were null.

=count(if(Chargeable = 'Yes' and isnull([Change Control Ref]), CCR Reference))

Karl

[#3]

Karl! You are brilliant! Thank you so much for fixing this for me

Your first option worked first time.

Frances