Joseph Jones
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.PayableAmou nt})
Please help me,
Joe
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.PayableAmou
Please help me,
Joe
ASKER
Hi
I tried as follows, but still it sums up all rows.
if {Refund_vw.Cancelled} = "No" then
SUM({Refund_vw.PayableAmou nt})
Else
0
I tried as follows, but still it sums up all rows.
if {Refund_vw.Cancelled} = "No" then
SUM({Refund_vw.PayableAmou
Else
0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
solved my problem. Thanks mlmcc
Joe
Joe
ASKER
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
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}
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
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
ASKER
Thanks,
That solved my problem
That solved my problem
You're welcome. Glad I could help.
James
James
Add a formula
if {Refund_vw.Cancelled} = "No" then
{Refund_vw.PayableAmount}
Else
0
Sum that to get the total
mlmcc