How to sum Null Values

[anushree1]

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

[prieper]

In the script:

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

[jonathandienst]

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?

[anushree1]

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

[el_aprendiz111]

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