?
Solved

Sum rows based on one field conditon

Posted on 2011-10-13
9
Medium Priority
?
251 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:JOLEEJJ
  • 4
  • 3
  • 2
9 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36966531
Try this idea

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

Sum that to get the total

mlmcc

0
 

Author Comment

by:JOLEEJJ
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

by:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 35

Expert Comment

by:James0628
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

by:JOLEEJJ
ID: 36977504
solved my problem. Thanks mlmcc
Joe
0
 

Author Comment

by:JOLEEJJ
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

by:James0628
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
    {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
0
 

Author Comment

by:JOLEEJJ
ID: 36984219
Thanks,
That solved my problem
0
 
LVL 35

Expert Comment

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

 James
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question