topic Re: How to count without null values? in QlikView

How to count without null values?

gireesh1216 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I want to count employees based on Ticket_No.If Ticket_No is null no need to count employee. Please find my below query.

=count({$<Emp_Status={'Completed'},Ticket_No={'>0'}>}Ticket_No)

help me

Re: How to count without null values?

Anil_Babu_Samineni — Mon, 19 Dec 2016 11:06:37 GMT

Try like this?

=count({$<Emp_Status={'Completed'},Ticket_No={"$(=Len(Ticket_No)>0)"}>}Ticket_No)

Re: How to count without null values?

marcus_sommer — Mon, 19 Dec 2016 11:11:09 GMT

NULL won't be considered by normal aggregation-functions therefore you should get with: =count({$<Emp_Status={'Completed'}>}Ticket_No) the correct result. To exclude records which aren't NULL but empty you could use: =count({$<Emp_Status={'Completed'},Ticket_No={'*'}>}Ticket_No).

- Marcus

Re: How to count without null values?

vinieme12 — Mon, 19 Dec 2016 11:28:50 GMT

=count({$<Emp_Status={'Completed'}>} if(NOT isnull(Ticket_No), Ticket_No))

Re: How to count without null values?

gireesh1216 — Tue, 20 Dec 2016 07:16:28 GMT

Above function is not working.I tried below expression it's working fine.

if(Ticket_No>0,Ticket_No,'null') as Ticket_No

Above expression i used in edit script window

=count({$<Emp_Status={'Completed'}>}Ticket_No)-count({$<Emp_Status={'Completed'},Ticket_No={' null'}>}Ticket_No)

Re: How to count without null values?

vinieme12 — Tue, 20 Dec 2016 07:58:13 GMT

if you want to count excluding null values then use operator (-=)

=count({$<Emp_Status={'Completed'} , Ticket_No -={' null'} >}Ticket_No)

Re: How to count without null values?

Tue, 20 Dec 2016 09:23:49 GMT

beckend:

if(Ticket_No>0,Ticket_No,null()) as Ticket_No

Try this in frontend

=count({$<Emp_Status={'Completed'},Ticket_No=-{''}>}Ticket_No)

Re: How to count without null values?

Tue, 20 Dec 2016 10:00:47 GMT

Hi,

generate new field in the script something like:

if(len(Ticket_No) > 0, 1, 0) as TickCnt

Formula in the FrontEnd

sum(TickCnt)

HtH

Re: How to count without null values?

Tue, 20 Dec 2016 10:03:53 GMT

and indeed invoke the set analysis

so:

sum({$<Emp_Status={'Completed'}>} TickCnt)

Re: How to count without null values?

qlik_jaijhn — Tue, 20 Dec 2016 10:09:26 GMT

Please try this :-

=Count({<Emp_Status={"Completed"}>}If(Len(Ticket_No)>0,1))

Re: How to count without null values?

Anonymous — Tue, 20 Dec 2016 11:18:01 GMT

Hi Gireesh,

Try below expression.

If(Emp_Status='Comlpeted' and not isnull(Ticket_No),Count(Ticket_No))

Warm regards,

Venkata Sreekanth

Re: How to count without null values?

diego_vazquez — Tue, 20 Dec 2016 11:36:56 GMT

Hi,

try the following expression:

Count(

If(Emp_Status = 'Completed' AND not isNull(Ticket_No),

Ticket_No

)

Re: How to count without null values?

jonathandienst — Tue, 20 Dec 2016 12:25:22 GMT

I recommens that you disregard ALL of the above responses except for Marcus Sommer's answer, as they are either wrong or do unnecessary extra work.

Re: How to count without null values?

gireesh1216 — Tue, 20 Dec 2016 12:44:30 GMT

Ok i will try