Solved

sum of a column between two dates

Posted on 2004-09-04
10
429 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 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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 

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

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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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