Dealing with zero/zero returning Null or '-'

[flames]

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 ?

[lblumenfeld]

If you use

Alt(Cost/Rev), 0)

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

[flames]

Thanks Lblumenfled.

so can i use

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

?

Thanks again.

[lblumenfeld]

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

[flames]

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 · Accepted solution]

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