How to calculate number of days base on the same field? 2

[thi123]

I need to calculate the number of days that the work was completed from “Spec Complete” to Dis Complete. I started calculating the number of days by deducting the Fin Date/time to OrderDt to start the beginning figure. However, I could not get the correct formula to calculate the number of days from Spec complete to Dis Complete. I tried If Else statement but got stumped. Can anybody figure this out? Formula must calculate the number of days from "Spec complete fin date/time 2/7/03 to Dis complete fin date/time 12/31/02.” I’ve been trying to figure this out for 3 days and going nowhere….HELP!!!! Would certainly appreciate it.

The following are the fields that I am extracting.

OrderDt Mlstone FinDt/Time #of days
11/21/2002 Dis comp 12/31/02 40
11/21/2002 Spec comp 2/7/03 ????
11/21/2002 Integ Inst 2/10/03 ????
11/21/2002 Integ tsted 2/15/03 ????
2/28/2003 Dis comp 3/6/03 ????
2/28/2003 Spec comp 4/10/03 ????
2/28/2003 Integ Inst 4/15/03 ????
2/28/2003 Integ tsted 5/20/03 ????

What I need to accomplish is this:

FinDt/Time # of Days
12/31/02 40
2/7/03 37 (2/7/03 - 12/31/02)
2/10/03 3

I could not come up with the correct formula in Crystal Report.

 

[tektipdjango]

First you want to group on orderDate so make a group on this.

then create 2 formulas :

One in the GroupHeader :

@OrderFormula

whilePrintingRecords;
numbervar previousDaysElapsed:=0;

The second in the detail section :

@DaysElapsed

whilePrintingRecords;
numbervar daysElapsed;
numberVar previousDaysElapsed;
daysElapsed=datediff("D",{FinDt},{OrderDt}) - previousDaysElapsed;
previousDaysElapsed=previousDaysElapsed+daysElapsed;
daysElapsed;

That's it!

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[thi123]

Thanks tecktipdjango for your quick response. I forgot to add that I am only using the order date to get the starting figure. The rest of the calculation will be based on the date that the Spec was completed minus the date that the Disc was completed.

I will try and see if your recommendation works. Will let you know. Thanks again.

 

[thi123]

Hi tektipdjango,

I used your recommended formula but it did not work. All I was getting was "Zero". I need to calculate the date difference between the time that the Spec was signed off and the Dis Form was delivered. So, I have to base the calculation on the FinDt/time. I cannot use the OrderDt to calculate the days elapsed. Must base it on the FinDt/Time.

Sample:

Milestone FinDt/Time # of days elapsed
DisFrmComplete 12/30/02 40
SpecSgnedOff 2/7/03 38 (based on "2/7/03-12/30/02")
DisFrmComplete 2/10/03 3 (based on "2/10/03-2/7/03")

Please help!!!!! I could not figure out the correct formula. Thanks in advance.

 

[tektipdjango]

In my opinion, the order date is just a "date reference" and the formulas and variables use it in this manner.

I haven't CR at the moment but it seems I make a mistake in the datediff function, so the solution would be:

First [highlight]you have to group on orderDate[/highlight] so make a group on this.

then create 2 formulas :

One in the GroupHeader :

@OrderFormula

whilePrintingRecords;
numbervar previousDaysElapsed:=0;

The second in the detail section :

@DaysElapsed

whilePrintingRecords;
numbervar daysElapsed;
numberVar previousDaysElapsed;
daysElapsed=datediff("D",[highlight]{OrderDt},{FinDt}[/highlight]) - previousDaysElapsed;
previousDaysElapsed=previousDaysElapsed+daysElapsed;
daysElapsed;

Please check datediff function syntax as I'm not quite sure of it!

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[lbass]

I think you could also accomplish this by using:

if onfirstrecord then {table.findate}-{table.orderdate} else
{table.findate} - previous({table.findate})

You have not indicated whether you have any groups. If you have a group, let's say on {table.acct}, then you would need to add in a running total {#cntwingrp}, which you could set up using the running total editor: Select {table.acct}, count, evaluate for each record, reset on change of group (Acct). Then change the formula to:

if {#cntwingrp} = 1 then {table.findate}-{table.orderdate} else
{table.findate} - previous({table.findate})

-LB

 

[thi123]

Thanks to both of you guys; tektipdjango and lbass. I got the report running and the output came out correct using lbass formula.