How to count without null values?

[gireesh1216]

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

[Anil_Babu_Samineni]

Try like this?

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

[marcus_sommer]

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

[vinieme12]

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

[gireesh1216 · Accepted solution]

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)

[vinieme12]

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

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

[#7]

beckend:

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

Try this in frontend

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

[#8]

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

F.

[#9]

and indeed invoke the set analysis

so:

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

[qlik_jaijhn]

Please try this :-

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