Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with NullCount combined with If Function

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 ReferenceDate Customer Approved SoWPOB_DateChargeableBudget CodeChange Control Ref
566444116219/09/2012YesNO259CC975
598934115019/09/2012YesPN190 1906CC973
5989541117-Yes--
599144121221/11/2012YesET100 2600  ST001-
5992341106-Yes66236-
599314112819/09/2012YesET100 2600 ST007-
599714115619/09/2012YesCA051 1400-
604134115221/11/2012YesPL012-
604154113419/09/2012YesSB013 1903-
604984115919/10/2012YesHR413 2104-
620854115919/10/2012YesHR413-

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

1 Solution

Accepted Solutions
Luminary
Luminary

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

View solution in original post

2 Replies
Luminary
Luminary

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

View solution in original post

Not applicable

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

Your first option worked first time.

Frances