2 Replies Latest reply: Feb 8, 2013 5:32 AM by flawrence RSS

    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