Discussion board for questions on how to use Qlik Community and its features.
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 ?
Go to 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.
If you use
then C will be 0 if the result of the division is null.
so can i use
D = SUM(Qty/Amt) + SUM (Sales/Amt) + Alt((Cost/Rev),0)
Yes. You might also consider using it for the other two division operations if their denominators could be 0.
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
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.