topic Re: Conditional Count expression in QlikView

Conditional Count expression

Anonymous — Wed, 06 Aug 2014 10:32:26 GMT

Hi,

I am new to QV world just learning basics. I am looking for help with conditional count expression. Searched here, but did not find what I needed.

I have two data columns - Name and Certification date. I want to create text box showing count of Names that are not certified (= empty cell for Certification date). What is the right syntax for expression?

Also what the right expression would be if I want this shown as percentage from total of Names?

Thanks for help.

Re: Conditional Count expression

Anonymous — Wed, 06 Aug 2014 10:40:19 GMT

Try

=count(if(not(isnull([Certification date])),Names))

Re: Conditional Count expression

tresB — Wed, 06 Aug 2014 10:42:35 GMT

=Count(Distinct If(Len(Trim([Certification date]))=0, Name) /Count(Distinct Name) *100

Re: Conditional Count expression

Yousef_Amarneh — Wed, 06 Aug 2014 10:45:07 GMT

create a flag in your script and name it IsCertified and but the following if(IsNull(Certification date),0,1) then reload the script

in your report set the following expression count({$<IsCertified{0}>} Name)

Re: Conditional Count expression

Anonymous — Wed, 06 Aug 2014 10:58:39 GMT

Thanks, but this doesn't work. Shows Error: Nested aggregation not allowed.

Re: Conditional Count expression

tresB — Wed, 06 Aug 2014 11:01:33 GMT

Sorry a bracket was missing. Try:

=Count(Distinct If(Len(Trim([Certification date]))=0, Name) ) /Count(Distinct Name) *100

Re: Conditional Count expression

Anonymous — Wed, 06 Aug 2014 11:04:18 GMT

Thanks. This works. I have just removed "not" so it counts empty cells.

Re: Conditional Count expression

Anonymous — Wed, 06 Aug 2014 11:08:13 GMT

Now works. Thanks a lot !

Re: Conditional Count expression

breno_morais — Tue, 21 Aug 2018 13:52:12 GMT

Thanks. You help me a lot, I did not know the "not"

Re: Conditional Count expression

hector_obregon_ — Thu, 18 Oct 2018 14:58:21 GMT

Thanks so much my friend...