Diffrence of a field based on min and max days

You can use DateDiff.

How are you selecting the records?

mlmcc

You really haven't given us much to work with.  There are too many outstanding questions:

1) What kind of difference need?  Days, years, months, etc...?
2)  Is there a time component?
3)  Does anything need to be excluded (weekends and holidays, for example)?
4)  Are the dates in actual date or datetime format or in a numeric or text format?
5)  Why are you using the Select Expert? Are you trying to filter the data to only those records where the difference is within a range of intervals (7 days, for example)?
6)  "Flat file" - do you mean it's some sort of physical file, like an Excel spreadsheet or text file or are you simply referring to the database table structure as a flat file (AS/400 tables are generally referred as physical files and their views are referred to as logical files, for example)

FYI , if you simply want the difference in days between two dates then you can simply create a formula to subtract one date field from another.  That always returns the number of whole or partial days (depending if there's a time component).

The others assumed you were looking for the difference between two dates, but I _think_ what you're saying is that you're going to be entering a starting and ending date, and you want to take the value in the Assets field for the first and last dates in that range, and take the difference between them.  IOW, see how much Assets changed during that period of time.

 Assuming that:
 1) I'm correct
 2) You're going to check the date range in the record selection formula (so the report only includes the records in that range)
 3) The report will be sorted by that date field
 4) You want to show the difference in the report footer

 you could try this.

 Create a formula like the following and put it in the report header:

WhilePrintingRecords;
Global NumberVar starting_assets := {Assets};
""

 The "" at the end is simply so that the formula doesn't produce any visible output on the report.  You could also suppress that field, or the section that the field is in.

 Create a formula like the following and put it in the report footer:

WhilePrintingRecords;
Global NumberVar starting_assets;
{Assets} - starting_assets


 All those formulas do is save Assets from the first record in the report in a variable, and then subtract that variable from Assets from the last record in the report.  If the records are sorted by date, that should work.

 If you actually want to do this at some group level (eg. the report is grouped by department and you want to see the change in Assets for each department), the same kind of thing could be done at the group level.


 If the report is not going to be sorted by that date field, that complicates things a little.

 If you want to show that difference somewhere before the report footer, that may complicate things quite a bit more.

 James

I am sorry for the original question. I am going to be selecting 2 dates via the Select Expert and I want to see what the change is in Assets for each fund, and each fund will be grouped.

This report is going to look at each fund that stored in a flat table and compare the 2 dates to see if any have a large change in value. In the past I have had 2 Access queries with each date in it and I would compare the values that way but I really wanted to do this all in Crystal Reports.

Do you need all the records between the dates or can you simply select based on the dates?

{YourDateField} = {?Date1} OR {YourDateFIeld} = {?Date2}

If you need all the records or if selecting by date won't work you can group by fund then in the group header pick the minimum and maximum dates and subtract

DateDiff("d",Minimum({YourDateField},{FundNameField}),Maximum({YourDateField},{FundNameField}))

mlmcc

I like the grouping way but I am not getting what I need.

I have 2 groups in the report now, GH1 is the Portfolio Name and the GH2 is the date.

The formula for the value on the GH1 field is

DateDiff("d",Minimum({Data.Date},{Data.PortfolioName}),Maximum({Data.Date},{Data.PortfolioName}))

It is giving me the value of 61 if there are 2 dates in the group and 0 if there is only one date in the group.

Attached is a screen shot of the report. (GH1 does not have a field name shoing right now)

The 0 makes sense since the minimum and maximum are the same.

Are all the dates the same day?
You have it grouped by the date but it also seems to be per month.

WHat values do you expect in each case?

mlmcc

The dates in the database are all month end values. I am trying to see what the month to month change is of the assets for each portfolio.

That worked!!! Thanks sooo much!