Previous Date

[rmiller11]

I know how to get the max date but how do I get the date directly proceeding it? Some records will not have a date proceeding but 90% will have many. I want to creat formulas based on previous date and most recent max date and need to be able to handle it is there is no preceeding date.

 

[bdreed35]

I am a little confused by what you are looking for. Are you looking for the day prior to the max date? Are you looking for the date from the Previous record?

What version of Crystal are you using and can you provide a sample layout so that someone here can help you better?

~Brian

 

[rmiller11]

Ok, example below:

ID Date Number
100 01/01/2003 6.25
100 02/18/2003 6.50
100 11/04/2003 7.25
200 01/01/2003 5.00
300 01/02/2003 11.00
300 05/11/2003 11.25

Above on Record ID 100, I want to subtract 6.50 from 7.25; on record 200, I want to say 0 cause there is no preceeding date. One record 300 (11.25 - 11.00)

 

[bdreed35]

Ok, I understand now.

Try this:

if (Count({table.Date},{table.ID}) > 1) AND
   ((OnLastRecord) OR 
    (next({table.ID}) <> {table.ID})) THEN
        {table.Number} - Previous(table.Number};

You will end up having zeros were the above condition is not met. You can either suppress the field when the values is zero, or you can add an else to the if statemtn above and then conditionally suppress the field when the field equals that value.

~Brian

 

[rmiller11]

That worked well, I have one more thing to do and thought of another senario.

ID Date Number
100 01/01/2003 6.25
100 02/18/2003 6.25
100 11/04/2003 7.25
200 01/01/2003 5.00
300 01/02/2003 11.00
300 05/11/2003 11.25

What if id 100 has the same number twice or three times before. I would like to put the date 01/01/2003 on with the previous formula you provided as that would be the first previous date of change of the number. In other words Empoyee ID 100 got an increase of 1.00 on 11/04/2003 from his original pay of 6.25 which was last set on 01/01/2003. See what I am doing? How do I display that date?

 

[bdreed35]

This formula might be a little clunky but it seemed to work in the sample report I created.

WhilePrintingRecords;
dateVar PreviousDate;

select TRUE
    case OnLastRecord  : PreviousDate
    case OnFirstRecord : PreviousDate := {table.Date} 
    case (Previous({table.Number}) <> {table.Number} AND
          Next({table.ID}) = {table.ID}) : PreviousDate := {table.Date}
    default: PreviousDate

PreviousDate;

~Brian

 

[rmiller11]

WhilePrintingRecords;
dateVar PreviousDate;

select TRUE
case OnLastRecord : PreviousDate
case OnFirstRecord : PreviousDate := ({PJRATE.effect_date})
case (Previous({PJRATE.rate}) <> {PJRATE.rate} AND
Next({PJRATE.rate_key_value1}) = {PJRATE.rate_key_value1})
: PreviousDate := {PJRATE.effect_date}
default: PreviousDate

On the case OnFirstRecord : PreviousDate := ({PJRATE.effect_date}) line it keeps saying Date is required here when I check the formula. Is there something else that needs to be in there?

 

[synapsevampire]

rmiller: Please post the expected output with the example data, I think that this is much simpler than most think.

Also note that you ignored the erquest to supply your Crystal version, rememebr that when dealign with any technical support, that would be on o their first questions.

I suspect that grouping and a Running Total might suffice here were there to be some technical specifications.

-k

 

[rmiller11]

Ok the version is 8.5 Development and data is below(I think I already explained this above.

ID Date Number
100 01/01/2003 6.25
100 02/18/2003 6.25
100 11/04/2003 7.25
200 01/01/2003 5.00
300 01/02/2003 11.00
300 05/11/2003 11.25

I want to see:
100 01/01/2003 1.00 11/04/2003
I am getting everything except for the first date of a previous rate. (01/01/2003)

 

[lbass]

This looks like the same issue with a new variation as in your previous post, so here is an adaptation of my previous recommendation:

You could group on {employeeID} and then on {table.wage} (ascending) and then sort by {table.effectivedate} in DESCENDING order--this would place the minimum date at the previous rate in the previous record position. Then use these formulas:

{@preveffdate}:
if {table.effectivedate} <> minimum({table.effectivedate},{table.employeeID}) then
previous({table.effectivedate}) else Date(0,0,0)

{@wagediff}:
if {table.wage} <> minimum({table.wage},{table.employeeID}) then {table.wage} - previous({table.wage}) else 0

Place these in the details section, and then go to format-> section->details->suppress->x+2 and enter:

{table.effectivedate} <> maximum({table.effectivedate}, {table.employeeID})

-LB

 

[Ngolem]

the way I would do it is to Group by ID and then by Date

Group 1 header ID (ascending)
Group 2 header Date (Descending) (grab the first date (max))
Details
Group 2 footer (present results)
Group 1 footer

//@MaxDateValues (suppressed in GP 2 header)

WhilePrintingRecords;
DateVar MaxDate;
numberVar MaxValue;

if not inRepeatedGroupHeader then
(
MaxDate := {Table.date};
MaxValue := {Table.number};
);


//@DisplayDifference (display in Group Footer 2 )
WhilePrintingRecords;
numberVar MaxValue;

MaxValue - {Table.number}; //{Table.number} is the Min value

//@DisplayMaxDate
WhilePrintingRecords;
DateVar MaxDate;
MaxDate;

Your Group 2 footer section would be

{Table.ID}{Table.Date}{@DisplayDifference}{@DisplayMaxDate}

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

LB seems to have a clean method, though I would simplify even more.

To state requirements as I understand them, you want the ID, minimum date, difference in number values, and maximum date.

So group by the number.

In the group header place a formula containing:
whileprintingrecords;
datevar Startdate := {table.date};
numbervar StartNumber:= {table.number};

In the group footer use a conditional formula for suppression of the group footer or on a field basis:

whileprintingrecords;
datevar Startdate;
numbervar StartNumber;
if {table.date} <> Startdate
and
{table.number} <> StartNumber then
blah blah blah

-k