?
Solved

sum of a column between two dates

Posted on 2004-09-04
10
Medium Priority
?
432 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 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
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 

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

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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