Link to home
Start Free TrialLog in
Avatar of Joseph Jones
Joseph JonesFlag for Australia

asked on

Sum rows based on one field conditon

Hi,

I need to show total amount payable on report footer after meeting a certain conditon.  Following is the conditon formula that I am using but it still bring sthe whole sum including the rows that do not meet the condtion. The formula field created is "TotalPayable amount".

if {Refund_vw.Cancelled} = "No" then
SUM({Refund_vw.PayableAmount})

Please help me,

Joe
Avatar of Mike McCracken
Mike McCracken

Try this idea

Add a formula
if {Refund_vw.Cancelled} = "No" then
    {Refund_vw.PayableAmount}
Else
    0

Sum that to get the total

mlmcc

Avatar of Joseph Jones

ASKER

Hi

I tried as follows, but still it sums up all rows.

if {Refund_vw.Cancelled} = "No" then
    SUM({Refund_vw.PayableAmount})
Else
    0
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think what you're missing is that the CR summary functions always produce the result for _all_ of the records.  They don't operate on a record by record basis.  So, when you say Sum ({field1}), that will always return the total of field1 in all records.  So, if you put your formula in the detail section, it would only produce a total on the records where Cancelled = "No", but it would always be the total for all records.

 A formula like the one that mlmcc suggested will only produce PayableAmount on the records where Cancelled = "No".  If you then do a summary on that formula, you get the total from just those records.

 James
solved my problem. Thanks mlmcc
Joe
Hi mlmcc

Now, I have a different scenario in calculation.  As per my previous scenario, I also need to subtract amount already paid from total amount payable and show it is as the balance amount.
For example, the TotalAmountPayable after using your code in the formula, is $73 (after excluding the condtion "No").  The AmountReceived which a value from the database field is $60. Therefore, the BalanceAmount should be calculated as $13 which is TotalAmountPayable minus AmountReceived.
I tried as follows:
{@TotalAmountPayable} - {Refund_vw.AmountReceived} but I get $-40 which is wrong. Can you please let know where I have gone wrong.

Thanks

Joe
Is AmountReceived like PayableAmount and should only be included when Cancelled = "No", or should the AmountReceived from every record be included?

 If it's like PayableAmount, then create a second formula like this:

if {Refund_vw.Cancelled} = "No" then
    {Refund_vw.AmountReceived}
Else
    0


 Then you should be able to get the total difference using:

 Sum ({@TotalAmountPayable}) - Sum ({new formula})


 If you want to include AmountReceived from every record (not just when Cancelled = "No"), then a formula like the one that you posted should work.  You'd do a Sum of that formula to get the total.  Or you could create a formula to calculate the difference between the totals:

 Sum ({@TotalAmountPayable}) - Sum ({Refund_vw.AmountReceived})


 James
Thanks,
That solved my problem
You're welcome.  Glad I could help.

 James