topic Sum NULL fields and return NULL expression? in QlikView

Sum NULL fields and return NULL expression?

phcaptjim — Tue, 17 Sep 2013 19:19:30 GMT

I am having some trouble getting the SUM of a NULL field to return a NULL value.

My data model is very simple. I have data that is entered manually to our DB on a monthly basis, and this is always done at the end of the month. The Numerator field is NULL until this data is entered, but it appears that the numerator is ZERO when you look at the table in QVW.

Example:

CLABSI Infections 0

CAUTI Infections 0

I wan this to display:

CLABSI Infections -

CAUTI Infections -

My expression is this:

=Sum(Numerator)

How do I adjust this expression to account for these NULL values?

Thanks!

Re: Sum NULL fields and return NULL expression?

jaimeaguilar — Tue, 17 Sep 2013 23:00:05 GMT

Hi,

I think its just a design issue. It has to do with the fact that QlikView interprets null and nothing as different things (they actually are). You may want to check the following post in order to understand the difference between both null and nothing: http://community.qlik.com/docs/DOC-3155

Regarding what you want to do, you can make a validation in your expression with something like this:

if(nullcount(CLABSI Infections), null(), CLABSI Infections), so the zeros in CLABSI and CAUTI Infections will be replaced with ' - '

regards

Re: Sum NULL fields and return NULL expression?

phcaptjim — Tue, 17 Sep 2013 23:53:27 GMT

Thanks for the reply Jamie.

I should have been more specific with my original post, but my expression actually has set analysis, so I am not sure NULLCOUNT will work with this? Any ideas?

SUM({<[Calendar Month Year]={'$(=date(max([Metric Date]),'YYYY-MM'))'}>} [NUMERATOR])

Re: Sum NULL fields and return NULL expression?

CELAMBARASAN — Wed, 18 Sep 2013 00:18:20 GMT

May be something like this

If(IsNull(MAX({<[Calendar Month Year]={'$(=date(max([Metric Date]),'YYYY-MM'))'}>}[NUMERATOR])), Null(), SUM({<[Calendar Month Year]={'$(=date(max([Metric Date]),'YYYY-MM'))'}>}[NUMERATOR]))

Re: Sum NULL fields and return NULL expression?

phcaptjim — Wed, 18 Sep 2013 00:39:10 GMT

Thanks Celam, that works fine. The easy solution!