How to include Null values within Set Analysis formula

[tcyw2910]

Hi all,

I am currently using a KPI to count the number of orders, and this is the formula below:

=count({$<[Global Orders Flag]={"Group"}, [Customer Name] -= {'customerName1'}>} distinct([Order]))

How do I go about including null values in the KPI count using this formula?

Thanks,

Terence

[tcyw2910]

to add to this, 

I need the Null values for [Customer Name], so essentially excluding CustomerName1 but including null values.

[Or]

I believe what you'll need here is:

{1-$<[Customer Name]={"customerName1"}>}

Have a look at the PDF attached to this post, specifically the last two pages, for explanation and additional details/options: https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472

[tcyw2910]

If I add the 1-$, that'll affect my selection for Global Order Flags, which I don't want. How do I apply that to my formula without affecting anything else other than Customer Name?

[Or]

Why would it impact another selection? It's not supposed to... should be something along the lines of:

=count({$<[Global Orders Flag]={"Group"}> * 1-<[Customer Name] = {'customerName1'}>} distinct([Order]))

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

See the "Identifiers" section in the documentation above in case my syntax isn't correct - I don't have data to test it with.

[agigliotti · Accepted solution]

Hi @tcyw2910 ,

Maybe this:

=count( {< [Global Orders Flag]={'Group'}, Order = {"=IsNull([Customer Name]) or [Customer Name]<>'customerName1'"} >} distinct Order )

I hope it can help.

Best Regards

[tcyw2910]

thank you! this worked for me 🙂