?
Solved

sum of a column between two dates

Posted on 2004-09-04
10
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 10

Accepted Solution

by:
ebolek earned 108 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 101

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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

770 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