Subtract Business Days - Replace NumberVar with Formula

[former_member773631]

Good day everyone. I'm trying to use a formula to subtract business days. I found a very useful formula from Ken Hamaday below. However I would like to replace the NumberVar Add := -3 with a formula. Essentially I'm trying to back date to a certain process stage in my route for scheduling purposes. The formula would look similar to the following:

IF {Process.Group} = "FINISHING" THEN SUM({Process.Lead_Days}) ELSE 0

I have also thought of using subtotals and running totals but crystal doesn't like those included in formulas.

//Subtracting Business Days:

WhileReadingRecords;

DateVar Array Holidays;

DateVar Target:= date ({ORDE_Goods.Agreed_Date}); //Put your field name in here

NumberVar Add:= -3; // Put in the number of days to subtract (a negative number)

NumberVar Added := 0;

WHILE Added > Add

Do (target := target -1;

if dayofweek (target) in 2 to 6 and not (target in holidays)

then Added:=Added-1

else Added:=Added);

Target

Thank you in advance,

Matt

[DellSC]

Do you know the logic you need for getting the number of days?

-Dell

[former_member773631]

Hi Dell,

The basic logic that I would need to get the number of lead days is as follows:

IF {Process.Group} = "FINISHING" THEN SUM({Process.Lead_Days}) ELSE 0

I was trying to include a subtotal calculation:

Sum ({Process.Lead_Days}, {ORDE.Job_Number})

but I don't think a subtotal can be referenced in a formula.

Thanks,

Matt

[DellSC]

Yeah, Crystal won't let you do a sum of a sum. Try this:

{@LeadDays}
WhileReadingRecords;
NumberVar LeadDays;
If OnFirstRecord or {ORDE.Job_Number} <>  Previous({ORDE.Job_Number} then
  LeadDays := 0;
If {Process.Group} = 'FINISHING" then LeadDays := LeadDays + {Process.Lead_Days};
""

This is a "manual summary". Put this on your report - probably in the Process Group header. It will set the variable to 0 at the start of the report and then every time the Job_Number changes. The "" at the end sets it so that it doesn't display anything but it does get calculated.

Then use something like this to do your calculation for the Job_Number:

//Subtracting Business Days:
WhileReadingRecords;
NumberVar LeadDays;
DateVar Array Holidays;
DateVar Target:= date ({ORDE_Goods.Agreed_Date}); //Put your field name in here
NumberVar Add:= LeadDays; // Put in the number of days to subtract (a negative number)
NumberVar Added := 0;
WHILE Added > Add Do (
  target := target -1;
  if dayofweek (target) in 2 to 6 and not (target in holidays) then 
    Added:=Added-1
  else 
    Added:=Added);

You may need to put this formula in the Job_Number group footer like you would if you were doing a running total.

-Dell

[former_member773631]

When I create the LeadDays formula Crystal gives me the following error:

"This function cannot be used because it must be evaluated later"

That's the same error I get when I try to use a subtotal in a formula.

Thanks,

Matt