topic Re: Sum fields with sometimes missing values in QlikView

Sum fields with sometimes missing values

Fri, 27 Mar 2015 09:56:45 GMT

Hi,

trying to sum up a few fields as per below.

Problem is, when data Is missing (most oftenly one or two fields are empty) which results in a "-" rather than a sum.

I need these missing values to be treated as zeroes to be able to calculate a sum for each date.

LOAD

A+B+C+D as DailyNet

FROM

(ooxml, embedded labels, table is Sheet1);

awhitfield — Fri, 27 Mar 2015 10:02:25 GMT

Hi Olle,

please check out the following link

Regards

Andy

trdandamudi — Fri, 27 Mar 2015 10:02:40 GMT

Put a check mark against "Suppress when value is null" and this will take care of the issue.

Fri, 27 Mar 2015 10:03:42 GMT

Hi olle

Try to in load for each field:

IF(IsNull(A),0,A) as A

prieper — Fri, 27 Mar 2015 10:06:14 GMT

use

RANGESUM(A, B, C, D) as DailyNet

Returns the sum of a range of 1 to N arguments. As opposed to the + operator, rangesum will treat all non-numeric values as 0.

HTH Peter

MK_QSL — Fri, 27 Mar 2015 10:09:37 GMT

Try

RangeSum(A,B,C,D) as DailyNet