Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Highlighted
peekay
New Contributor II

Dealing with zero/zero returning Null or '-'

I am writing a formulae, which is an addition of 3 formulaes.

for example,  A+B+C = D

A = Qty/Amt = 3

B= Sales/Amt= 1

C = Cost/Rev = 0/0  = returning '-' 

as a result of that my calculation of 'D' results in '-'. 

But i want D to be 3+1+0 = 4 else just 3+1 = 4 .

 

How to deal with values when it comes zero/zero ?

Labels (2)
1 Solution

Accepted Solutions
lblumenfeld
Contributor

Re: Dealing with zero/zero returning Null or '-'

You have a misplaced parenthesis ")".

Paste this in.

Sum(Aggr( alt(SUM(A/B),0) + Alt(SUM(C/D),0) + Alt(SUM(E/F),0) ), col1, col2)

Please let me know if this works for you and if so then mark the reply as a solution.

6 Replies
lblumenfeld
Contributor

Re: Dealing with zero/zero returning Null or '-'

If you use

Alt(Cost/Rev), 0)

then C will be 0 if the result of the division is null.

peekay
New Contributor II

Re: Dealing with zero/zero returning Null or '-'

Thanks Lblumenfled.

so can i use

D = SUM(Qty/Amt) + SUM (Sales/Amt) + Alt((Cost/Rev),0)

?

Thanks again.

lblumenfeld
Contributor

Re: Dealing with zero/zero returning Null or '-'

Yes. You might also consider using it for the other two division operations if their denominators could be 0. 

peekay
New Contributor II

Re: Dealing with zero/zero returning Null or '-'

i am using it insode an aggr function.

 

the formulae looks like 

 

Sum(Aggr(SUM(A/B)+SUM(C/D)+SUM(E/F)), col1, col2)

somehow, it's not working if i use

Sum(Aggr( alt(SUM(A/B),0) + Alt(SUM(C/D),0) + Alt(SUM(E/F),0) ), col1, col2)

 

lblumenfeld
Contributor

Re: Dealing with zero/zero returning Null or '-'

You have a misplaced parenthesis ")".

Paste this in.

Sum(Aggr( alt(SUM(A/B),0) + Alt(SUM(C/D),0) + Alt(SUM(E/F),0) ), col1, col2)

Please let me know if this works for you and if so then mark the reply as a solution.

Re: Dealing with zero/zero returning Null or '-'

Alt() is very useful to return a defined default-value if no parameter returned a numerical value but I think in your case would a rangesum() be more handy because each not numerical parameter would be treated as 0.

- Marcus

Community Browser