Sum NULL fields and return NULL expression?

[phcaptjim]

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!

[jaimeaguilar]

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

[phcaptjim]

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

[CELAMBARASAN · Accepted solution]

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

[phcaptjim]

Thanks Celam, that works fine.  The easy solution!