Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
if(SUM(QTY_ACTUAL) = 0,0,SUM(to_collect))
I want it to show sum of 65.
my problem is that it gives me a sum of 115.
Thanks in advance
Yaniv
Use the following expression instead.
=Sum({<QTY_ACTUAL={">0"}>} to_collect)
Or you could do
Sum(
If(QTY_ACTUAL = 0, 0, To_Collect)
)
But i would think the set analysis expression Krishnamoorthy posted is faster
For future reference, all you needed was to change the order of if and sum.
Think of it this way, what do you want to do first.
In this case you want to check line by line to find 0 QTY and since sum is aggregate, you have to do the line by line inside the sum.
So it becomes SUM(IF(Something))
If you do this IF(SUM(Something)) then you first sum it and then check if its 0.
Best wishes
Thanks for your help and for the explanation of the sum/if order.
Thanks,
I have applied your answer and it works fine.
I tried to add to it a condition that calculate only the qty with family = 10 but for some reason I am getting the wrong result again.
the formula i'm using is
if(sum({<FAMILY={'10'}>}QTY_ACTUAL) = 0,0,sum({<FAMILY={'10'}>}To_Collect))
when I am changing the TOTAL MODE from "expression total" to SUM of Rows it gives me the correct result but when I'm tring to add a column that gives the % that was actualy collected only for that was collected it sum the all To_Collect.
I want to get
(8+40+5) / (10+50+5) which is 81.53%
but insted I get
(8+40+5) / (10+50+30+20+5) which is 46.08%
Thanks