Excluding row from count if specific column is null

[cawestelaken]

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.

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

[niclaz79 · Accepted solution]

Hi,

Try this:

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

[OmarBenSalem]

try:

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

[vamsee]

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]

Sadly this does not work. Thanks for trying though!

[cawestelaken]

This indeed seems to work! Thank you very much!

[cawestelaken]

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

[niclaz79]

Dollar expansions, dude

[OmarBenSalem]

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

[sunny_talwar]

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