topic Re: How to sum Null Values in QlikView

How to sum Null Values

anushree1 — Tue, 21 Nov 2017 08:00:03 GMT

HI,

I would like to create a field in the script which returns Null(Meaning '-') incase i Perform Sum Function on top of it.

I tried the below ways to create the field but when sum is applied on the field it returns 0.

Eg:

'' As Test

Null() AS Test

'-' as Test

None of them return '-' instead they return 0 when sum is applied on it like Sum(Test)

Can anyone please let me know how this could be done, so that i get '-' as the result

Re: How to sum Null Values

prieper — Tue, 21 Nov 2017 08:20:34 GMT

In the script:

IF(SUM(MyValue) = 0, NULL(), SUM(MyValue) AS ....

Re: How to sum Null Values

jonathandienst — Tue, 21 Nov 2017 08:24:11 GMT

Sum aggregates many values. QV skips over nulls, so if all the values are null, then Sum returns 0 as there was nothing to sum.

So do you want to show a null any value is null, or if all values are null?

Re: How to sum Null Values

anushree1 — Tue, 21 Nov 2017 10:22:37 GMT

i have a pick match expression which returns a field upon which i perform sum() in a chart.

so incase the pick match does'nt yield the right expression i want the sum() in chart to return '-'.

Please let me if this is not clear

Re: How to sum Null Values

el_aprendiz111 — Tue, 21 Nov 2017 15:11:11 GMT

Hi,

2 options:

tmp:
LOAD *,
If(Len(Data) = 0,1,0) AS MyNull,
If(Len(Data) = 0, RangeSum(1, Peek('CountNull')), RangeSum(0, Peek('CountNull'))) AS CountNull;
;
Load * inline
[
ID   ,Data
1    ,123
2    ,23
3    ,
4    ,
5    ,
6    ,
7    ,453
8    ,143
9    ,1
10   ,
11   ,211
12   ,
13   ,
14   ,4892
];