Solved

# Sum rows based on one field conditon

Posted on 2011-10-13
Medium Priority
251 Views
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})

Joe
0
Question by:JOLEEJJ
• 4
• 3
• 2

LVL 101

Expert Comment

ID: 36966531
Try this idea

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

Sum that to get the total

mlmcc

0

Author Comment

ID: 36966560
Hi

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

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

LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 36966582
That isn't what I said to do.

Create a formula without the SUM
if {Refund_vw.Cancelled} = "No" then
{Refund_vw.PayableAmount}
Else
0

Insert it into the report
Right click it
Click INSERT --> SUMMARY
Put the summary where you want it.

mlmcc
0

LVL 35

Expert Comment

ID: 36972893
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
0

Author Closing Comment

ID: 36977504
solved my problem. Thanks mlmcc
Joe
0

Author Comment

ID: 36977567
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
0

LVL 35

Expert Comment

ID: 36982162
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
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:

James
0

Author Comment

ID: 36984219
Thanks,
That solved my problem
0

LVL 35

Expert Comment

ID: 36998816
You're welcome.  Glad I could help.

James
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā¦
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. ā¦
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased riskā¦
###### Suggested Courses
Course of the Month12 days, 17 hours left to enroll