sum vs expression total

[egoziyan]

I have a table with the following data

to_collect, QTY_ACTUAL

10,8

50,40

30,0

20,0

5,5

I use the following command

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

[nagaiank]

Use the following expression instead.

=Sum({<QTY_ACTUAL={">0"}>} to_collect)

[calvindk]

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

[egoziyan]

Thanks for your help and for the explanation of the sum/if order.

[egoziyan]

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