Avatar of wilpitz
wilpitz
 asked on

Diffrence of a field based on min and max days

I have a crystal report that is hitting a flat file with many dates in it. I am going to be selecting 2 dates and I need to see the diffrence between the [Assets] field from the 2 dates I select (the min date and the max date). The dates will be selected in the Select Expert.

Thanks!!

Chad
Crystal Reports

Avatar of undefined
Last Comment
wilpitz

8/22/2022 - Mon
Mike McCracken

You can use DateDiff.

How are you selecting the records?

mlmcc
Kurt Reinhardt

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).
James0628

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
wilpitz

ASKER
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.
Mike McCracken

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
wilpitz

ASKER
captureI 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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mike McCracken

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
wilpitz

ASKER
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.
ASKER CERTIFIED SOLUTION
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
wilpitz

ASKER
That worked!!! Thanks sooo much!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck