Compare Dates 1

[Simon9999]

Hi

I am using Crystal Xi.

I am putting together a report based on submission dates for bids. Each bid requires an approval priot to its submission and there is a 2 weekly approval meeting.

I therefore have a list of approval dates, e.g.

1/10/08
15/10/08
29/10/08
etc. etc.

As well as a submission date (e.g. 10/10/08).

I would like to compare the submission date to the approval date and display the approval date immediately before the submission date (e.g. in the above example, this would be 1/10/08).

Any ideas how I can do this?

Thanks

Simon

 

[dgillz]

Why would simply displaying both dates on the report not work?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[Simon9999]

I want to display both dates but only display the appropriate approval date. The list will contain dates for the next 2 years and therefore I need to pull out just the date closest to the submission date.

Cheers

Simon

 

[IanWaterman]

YOu could just filter data in select statement

approvaldate <= submissiondate

Ian

 

[Simon9999]

Trouble is I am working from views and therefore cant ammend the SQL.

 

[IanWaterman]

I meant In the Crystal select Expert.

Ian

 

[Simon9999]

Thanks

However im not sure how this would pick up the relevant date from a list for each submission date on the report.

Cheers

Simon

 

[IanWaterman]

Can you show us a sample of raw data, and then a sample layout of what you want to see.


Ian

 

[Simon9999]

Hi

I have a list of approval meeting dates like the below

01/10/2008
08/10/2008
15/10/2008
22/10/2008
29/10/2008
05/11/2008
12/11/2008
19/11/2008
26/11/2008
03/12/2008
10/12/2008
17/12/2008
24/12/2008
31/12/2008

I then want to select the Approval date based on the submission date. The report wouild look similar to the below.
Name Submission Date Approval Date
Bid 1 08/10/2008 01/10/2008
Bid 2 09/10/2008 08/10/2008
Bid 3 30/10/2008 29/10/2008
Bid 4 01/11/2008 29/10/2008
Bid 5 15/12/2008 10/12/2008
Bid 6 05/01/2009 31/12/2008


Cheers

simon

 

[beltmanjr]

Create a group based on submission date
Make sure that the report sort order has the first detail sort level set on approval dates (ascending).

In the group footer place approval dates & submission date
next to each other. Run the report and you should see something like:

submission approval
1/10/08
2/10/08
3/10/08
--------
3/10/08 10/10/08

15/10/08
18/10/08
20/10/08
--------
20/10/08 24/10/08

If this works well you see the last submission date next to the appropriate approval date in the group footer. Now simply suppress the detail section to only see the last submission date and appropriate approval date.

 

[lbass]

You could handle this in two steps. First use Ian's idea and go to report->selection formula->record and enter:

{table.approvaldate} <= {table.submissiondate}

Then create a formula {@diff}:

datediff("d",{table.approvaldate},{table.submissiondate})

Insert a group on {table.bid} and then go to report->selection formula->GROUP and enter:

{@diff} = minimum({@diff},{table.bid})

-LB

 

[IanWaterman]

How do you know which is the Submission date.

Is it a separate field, if not how is it identified from the approval dates?

Ian

 

[Simon9999]

Thanks for this,

The Submission date is one of the fields in a table. However the list of approval dates simply sits outside of the database as a list of dates. I was thinking I would have to add these in as an excel or access file but there is nothing to link to in the main database.

Cheers

Simon

 

[beltmanjr]

LBass,
although a great way of doing this, I'm a little bit cautious using the group selecting as it does hide details, but does actually evaluate hidden details in summations.

I ran into this whilst creating a report the other day where I needed a max key and the data belonging to that key only to be summed.

A normal sum clearly included more than only the values belonging to the max key whilst these 'extras' were not visible at any level in the report. A running total resolved this issue, but still you must be well aware of this.

 

[lbass]

Yes, with group selection you have to use running totals if you are calculating across groups.

-LB

 

[Simon9999]

Thanks all for your help with this. However I dont think I was clear enough with this one. Basically the approval date is held in a seperate unrelated table or I could hold them in an array. However how do I get the submission date to reference the most recent date?

Thanks

simon

 

[lbass]

It is unclear what the issue is. That the submission date is in another table does not seem relevant. Try showing sample data at the detail level that uses both fields and that illustrates the issue, and then show the result you would expect to see.

-LB

 

[Simon9999]

Ok

I have a sql database with an opportunity table with details such as:

ID Name Submission Date
24 Oxford 24/10/08
25 Cambridge 30/10/08

I then have a completeley seperate list of approval dates which is stored in an excel spreadsheet e.g.

20/10/08
25/10/08
01/11/08

I would like the submission date for each row to be compared to the list of approval dates and the approval date that falls before the submission date to be included on each row. For example:

ID Name Submission Date Approval Date
24 Oxford 24/10/08 20/10/08
25 Cambridge 30/10/08 25/10/08

I have tried grouping things but as there is no common link between the submission date and the list of approvals the same list is repeated.

Cheers

Simon

 

[lbass]

So the approval dates are general and unrelated to an ID field? Have you linked the tables? You should just link the tables with an equal join using a <= join FROM the approval date TO the submission date. Then follow my previous suggestion:

Create a formula {@diff}:

datediff("d",{table.approvaldate},{table.submissiondate})

Insert a group on {table.bid} and then go to report->selection formula->GROUP and enter:

{@diff} = minimum({@diff},{table.bid})


-LB