Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
cawestelaken
New Contributor II

Excluding row from count if specific column is null

Hello reader,

I'm trying to exclude a row from a count if a specific column in this row is null.

More specifically to better describe the situation:

Service management, assigned and unassigned incidents. I'd like to count the tickets per priority that have been assigned to an employee.

count(${<[Incident.HuidigeActeur]<>null>}[_Flag_IncidentGesloten])

This is the formula I was trying to use (apologies for the Dutch field names)

To test whether or not it actually was excluding the null values I created the table below.

Capture2.PNG

As you can see it is still counting the incidents that do not have an employee assigned to it. This makes me feel that my Set Analysis is incorrect, but if I'm doing something else incorrect please do inform me.

I've also tried the following formula:

count(${<not isNull([Incident.HuidigeActeur])>}[_Flag_IncidentGesloten])

Does anyone know how I can achieve this?

Yours sincerely,

Casper Westelaken

1 Solution

Accepted Solutions
Partner
Partner

Re: Excluding row from count if specific column is null

Hi,

Try this:

count(if([Incident.HuidigeActeur]<>null(),[_Flag_IncidentGesloten]))

View solution in original post

13 Replies
Partner
Partner

Re: Excluding row from count if specific column is null

Hi,

Try this:

count(if([Incident.HuidigeActeur]<>null(),[_Flag_IncidentGesloten]))

View solution in original post

Partner
Partner

Re: Excluding row from count if specific column is null

try:

count(${<[Incident.HuidigeActeur]={"=len(trim([Incident.HuidigeActeur]))<>0"}>}[_Flag_IncidentGesloten])

vamsee
Valued Contributor

Re: Excluding row from count if specific column is null

Hi Casper,

Try

count(${<[Incident.HuidigeActeur]={'*'}>}[_Flag_IncidentGesloten])


Also you might have to add the set in all the expressions, not just the count one.

cawestelaken
New Contributor II

Re: Excluding row from count if specific column is null

Sadly this does not work. Thanks for trying though!

cawestelaken
New Contributor II

Re: Excluding row from count if specific column is null

This indeed seems to work! Thank you very much!

cawestelaken
New Contributor II

Re: Excluding row from count if specific column is null

This does not seem to work, thanks for trying though!

Partner
Partner

Re: Excluding row from count if specific column is null

Dollar expansions, dude

Partner
Partner

Re: Excluding row from count if specific column is null

No need for it Niclas;

within the condition in a set analysis; if the condition is an expression like max(Year) it's written as follow:

Year={"$(=max(Year))"} with the $() to force the calculation

But when it's an expression compared to sthing; example

I want to count the Region that have sales > 200

it would be :

Count(disinct{<Region={"=sum({1}Sales)>200"}>} Region) without the $() because it's already calculated while comparing to the 200.

Hope that was clear enough

Re: Excluding row from count if specific column is null

I think the problem with Omar and vamsee response is that they have the $ outside of the curly parenthesis... try this

Count({$<[Incident.HuidigeActeur]={'*'}>}[_Flag_IncidentGesloten])

or this

Count({$<[Incident.HuidigeActeur] = {"=Len(Rrim([Incident.HuidigeActeur]))<>0"}>} [_Flag_IncidentGesloten])

and both should work if the value are true nulls