Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
flames
Contributor III
Contributor III

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
Partner Ambassador
Partner Ambassador

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.

View solution in original post

6 Replies
lblumenfeld
Partner Ambassador
Partner Ambassador

If you use

Alt(Cost/Rev), 0)

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

flames
Contributor III
Contributor III
Author

Thanks Lblumenfled.

so can i use

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

?

Thanks again.

lblumenfeld
Partner Ambassador
Partner Ambassador

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

flames
Contributor III
Contributor III
Author

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
Partner Ambassador
Partner Ambassador

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.

marcus_sommer

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