topic Re: Set Analysis omitting nulls in QlikView

Set Analysis omitting nulls

quilldew — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Set Analysis omitting nulls

Anil_Babu_Samineni — Wed, 24 May 2017 13:15:49 GMT

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])

Re: Set Analysis omitting nulls

quilldew — Wed, 24 May 2017 13:25:02 GMT

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.

Re: Set Analysis omitting nulls

quilldew — Wed, 24 May 2017 13:35:03 GMT

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.

Re: Set Analysis omitting nulls

sunny_talwar — Wed, 24 May 2017 13:47:41 GMT

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

Re: Set Analysis omitting nulls

Kushal_Chawda — Wed, 24 May 2017 13:49:18 GMT

try this

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

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

Re: Set Analysis omitting nulls

quilldew — Wed, 24 May 2017 14:18:03 GMT

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

Re: Set Analysis omitting nulls

sunny_talwar — Wed, 24 May 2017 14:36:44 GMT

More like how QlikView's set analysis handles null