Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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

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