Qlik Community

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
flames
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)
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.

flames
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.

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. 

flames
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)

 

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.

MVP & Luminary
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