Solved

sum of a column between two dates

Posted on 2004-09-04
10
419 Views
Last Modified: 2011-09-20
I want to do the following: where Beginning_Extraction_date and Extraction_date are parameters set to the report(entered by the end users)

1)Sum(table.Quantity) across all table.lot_numbers and all table.vendor_locations where table.Extraction_Date=Beginning_Extraction_Date

2)Sum of (table.quantity_received) across all table.Lot_numbers where table.Extraction_date between Beginning_Extraction_date and Extraction_date
0
Comment
Question by:asnath
  • 5
  • 4
10 Comments
 
LVL 10

Accepted Solution

by:
ebolek earned 36 total points
ID: 11982607
you have to create create two frmulas for that

formula 1:
1)Sum(table.Quantity) across all table.lot_numbers and all table.vendor_locations where table.Extraction_Date=Beginning_Extraction_Date
numbervar total;
if {table.extractiondate} = {?parameetr_beginningextractiondate} then
total := total + {table.quantity};
else
total := total;
total;

formula 2
2)Sum of (table.quantity_received) across all table.Lot_numbers where table.Extraction_date between Beginning_Extraction_date and Extraction_date

numbervar total2;
if {table.extractiondate} >= {?parameetr_beginningextractiondate} or {table.extractiondate} <= {?parameetr_extractiondate} then
total2 := total2 + {table.quantity};
else
total2 := total2;
total2;
 
You ca do this inconditionally by using running totals too but you have to writ the same criteria for them too. And it is better for you to learn the crystal syntax to write formulas.


Regards
Emre
0
 

Author Comment

by:asnath
ID: 11983139
Thanks a lot! Emre...I really appreciate it.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11983169
Glad to help. I have been frustrated alot with crystal so i know your pain

Regards
Emre
0
 

Author Comment

by:asnath
ID: 11986258

I get an error as : "The remaining text does not appear to be part of the formula"

this part is highlighted:
else
total2:=total2;
total2;


Do u think this part is necessary for the formula or we can ignore it. Please advise..Thanks!
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11986352
if (({table.extractiondate} >= {?parameetr_beginningextractiondate}) or ({table.extractiondate} <= {?parameetr_extractiondate})) then
total2 := total2 + {table.quantity};
else
total2 := total2;
total2;

that error message states that we forgot a paranthesis or field name used is wrong

try this foemula.And yes that part is needed because it makes the sum

Regards
Emre
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
ID: 11986652
I suspect the error is because of the ; before the ELSE

Try
if {table.extractiondate} = {?parameetr_beginningextractiondate} then
total := total + {table.quantity}
else
total := total;
total;

numbervar total2;
if {table.extractiondate} >= {?parameetr_beginningextractiondate} or {table.extractiondate} <= {?parameetr_extractiondate} then
total2 := total2 + {table.quantity}
else
total2 := total2;
total2;

mlmcc
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11986818
mlmcc
; in the formulas are used to seperate multiple ones but stii if there is one line it shouldnt be the promlem

Regards
Emre
0
 

Author Comment

by:asnath
ID: 11996581
numbervar total2;
if {table.extractiondate} >= {?parameetr_beginningextractiondate} or {table.extractiondate} <= {?parameetr_extractiondate} then
total2 := total2 + {table.quantity};
else
total2 := total2;
total2;


The error was becoz of the ; and i didnt get the error if i take off the ; before else.

also i have a question, in this formula i want to get the records that is between beginningextraction date and extraction date,so...instead of OR shouldnt I be using AND as follows:

numbervar total2;
if {table.extractiondate}>={?parameter_beginningextractiondate} AND {table.extractiondate}<={?parameter_extractiondate} then
total2:=total2 + {table.quantity}
else
total2:=total2;
total2;

As well, for total2 shouldnt it be having an intitialization value like

numbervar total2=0;
Please advise!
Thanks a lot for your guidance! I appreciate it!
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12000310
yes if you want to get the dates between those dates you should use and

Regards
Emre
0
 

Author Comment

by:asnath
ID: 12003269
As well, for total2 shouldnt it be having an intitialization value like

numbervar total2=0;
Please advise!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now