Set Analysis omitting nulls

[quilldew]

Hi,

I have a problem where I trying to filter a count() using set analysis, but it is also omitting nulls.

Imagine I have the expression

count([Vet])

this gives me a result for my dimension of 35, and

count({$<ReferralDischargeReason={'Admin Error'}>}[Vet])

gives me a result for my dimension of 1

I would assume that the expression

count({$<ReferralDischargeReason-={'Admin Error'}>}[Vet])

would give me a result of 34. It doesn't though, it give me a result of 18, because it also not counting the NULLs in the ReferralDischargeReason field, even though it counts them when I use the count with no set analysis.

Any idea why this is?

[Anil_Babu_Samineni]

Could be the reason you have 16 Null values for Vet? Can you check the same

Try this, And tell us how much value return

count({$<ReferralDischargeReason={'*'}-{'Admin Error'}>}[Vet])

[quilldew]

Hi Anil,

I actually have way more NULL values within 'Vet' in my table, literally thousands, and it isn't counting them when I use count([Vet]). I tried your code

count({$<ReferralDischargeReason={'*'}-{'Admin Error'}>}[Vet])

and it gave me the same result of 18.

[quilldew]

I'm now checking my data more thoroughly and I can now only find 19 with Vet marked that fall within my dimension. So it looks like the original count(Vet) is wrong because I have way way way more than 16 NULL values within Vet. I'll be back.

[sunny_talwar · Accepted solution]

I would suggest you to create flags in your script to address this issue.

If(Len(Trim(ReferralDischargeReason)) = 0, 'Null', ReferralDischargeReason) as NewReferralDischargeReason

Now use this new dimension in your set analysis, or use true flags with 0s and 1s

[Kushal_Chawda]

try this

count({1-<ReferralDischargeReason-={'Admin Error'}>}[Vet])

or

count({$-1<ReferralDischargeReason-={'Admin Error'}>}[Vet])

[quilldew]

This worked fantastic thanks. Is this just down to the way that Qlikview handles Nulls?

[sunny_talwar]

More like how QlikView's set analysis handles null