Solved

sum of a column between two dates

Posted on 2004-09-04
10
421 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
display only completed quarters in SQL script 4 53
cross tab crystal reports - filter a row 7 58
C# InstallShiled - Multiple Projects 3 70
Speed Up Crystal Report 5 56
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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