sum A based on field B condition
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1552
Printed Date: 07 May 2024 at 5:36am
Topic: sum A based on field B condition
Posted By: SAN2007
Subject: sum A based on field B condition
Date Posted: 23 Oct 2007 at 3:25am
|
Hi there! This is my first post and I am new on Crystal Reports.
I need your help on this:
I have
fields:
tableA.id (number)
tableA.date
tableA.qtyproduct (number)
tableA.year
tableB.id
tableB.date
tableB.condition (delivered, partial_delivered)
tableB.year
Relations: id=id, year=year (RIGHT JOIN)
I want to sum all qtyproducts with condition "delivered". I grouped by tableB.date, by quarter and month inside quarter. I tryed a formula:
if {tableB.condition} = "delivered"
then {tableA.qtyproduct}
else 0
and I place it in the detail section, then I summarized it and... I got the sum of tableA.qtyproduct that do not meet the condition "delivered".
What is wrong?
 Eg:
tableA.qtyproduct = 1 -- delivered
= 2 -- partial delivery
After the formula, I got 2 instead 1.
Would you please help me?
 Thank you very, very much! (and thanks for the forum, it is great!
 ) |
Replies:
Posted By: BrianBischof
Date Posted: 23 Oct 2007 at 9:53am
|
At first I thought that I knew what your problem was, until I got to the end. Your formula looks okay, so I don't know why you are getting a summary on the partial delivery. Something must be slightly off b/c it looks like it should work. Also, anytime you do summary formulas on groups, make sure you create your own formulas and put them inside the Details section. If you use running totals, then it ignores the group condition and summarizes all data (I discuss this on page 135 of my new book). Glad you like the forum - please link to it on a blog or website so other people know about it.  ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: SAN2007
Date Posted: 23 Oct 2007 at 4:33pm
|
Hi Brian! Thank you for your prompt response. There is no way. I will keep on trying. If I find out what was wrong I let you know.
Thanks for your time Brian. I will get your book.
Sandra
|
Posted By: wattsjr
Date Posted: 24 Oct 2007 at 10:01am
|
Hi SAN2007,
I agree with Brian that formula looks OK, but if I had this problem, I'd want to see what was actually happening in the formula. So I'd do the following as a debugging technique.
1. Create 2 formulas. The 1st one (called D) would be like yours:
if {tableB.condition} = "delivered"
then {tableA.qtyproduct}
else 0
the 2nd one (called P) would be the reverse of "D"
If {tableB.condition} = "partial delivery"
then {tableA.qtyproduct}
else 0
2. Place both formulas in your detail along with {tableB.condition}
This should let you see what the {...condition} value is along with the results of the formulas.
As a side note, if you don't need to see 'detail' for "partial delivery" items, you could exclude them using the Select Expert (i.e. {tableB.condition} = "delivered")
Regards,
------------- -jrw |
Posted By: SAN2007
Date Posted: 24 Oct 2007 at 4:09pm
|
Hi JRW!
Thaaaankkk you for your help. We all were right. I made it up!!!!
You will never believed what happened: there was an extra relation. Donīt ask me why or what but there it was. When I realized, I removed the third relation and there we were! It works.
Thank you for your time and assistant.
I will be around.
Sandra
|