Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
New 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)

• zero by zero

1 Solution

Accepted Solutions
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
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.

New Contributor III

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.

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.

New Contributor III

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)

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.

MVP & Luminary

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