null values in Set analysis

[#1]

Hello all,

I have the following expression I am using in straight table.

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}>

+

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}>}

DISTINCT Asset_Id)

It basically has two conditions each. I want those units which dont have contract or which have expired contract. Hence I am using '+' sign.

Now I want to add another condition which looks at contract status and excludes 'Active' or 'Inactive'. So, I am making my expression like below:

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}, EntStatus = {'*'} - {'Active','Inactive'}>

+

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}, EntStatus = {'*'} - {'Active','Inactive'}>}

DISTINCT Asset_Id)

But by doing so it also excludes null values. I want to include null values and exclude just 'Active' and 'Inactive'.

Please help me.

Thank you

Parth

[sunny_talwar · Accepted solution]

How about giving this a show:

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}, Asset_Id = {"=If(Match(EntStatus, 'Active', 'Inactive'), 0, 1) = 1"}>

+

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}, Asset_Id = {"=If(Match(EntStatus, 'Active', 'Inactive'), 0, 1) = 1"}>}

DISTINCT Asset_Id)

[#3]

Hi Sunny,

Thanks for your response. Still no luck.

-Parth

[sunny_talwar]

How about this:

=Count

({$<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntMissingFlag = {'Yes'}, Asset_Id = {"=NOT EntStatus LIKE 'Active' and NOT EntStatus LIKE 'Inactive' "}>

+

<Asset_UnitType = {'*'} - {'RMA','SA Demo Unit','Internal Use'}, EntRenewalStatus2 = {'Not Renewed'}, Asset_Id = {"=NOT EntStatus LIKE 'Active' and NOT EntStatus LIKE 'Inactive' "}>}

DISTINCT Asset_Id)

[#5]

Still the same result. Appreciate your help.

-Parth

[sunny_talwar]

Would it be possible to share a sample?

[#7]

Hi Sunny,

Apologies for not getting back. Actually, I was testing the result. Both of the above answers worked. I was measuring a wrong expression before.

Again apologies for not testing the result thoroughly.

Again tank you very much for your help.

Best

Parth