Calculating Days in a month based on Current Date 1

[BradCustom]

Hi, thanks for your help with this problem!

I'm using Crystal 2011 and I have a report which shows the actual sales vs. sales goals for a given time period. The time period is defined by the user entering a start and end date into a parameter called InvoiceDate . Typically the start and end of a month. What I would like to do is automatically select the start and end date based on the current date. For example; if I ran the report today 10/17/2016 then my start date would be 10/1/2016 and the end date would be 10/31/2016. below is the formula I'm currently using to calculate the work days between the start and end of range.

WhileReadingRecords;
Local DatetimeVar Start := {@MinDate};   // place your Starting Date here
Local DatetimeVar End := {Invoice_Header.Document_Date};  // place your Ending Date here
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 2 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and 
     Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days

 

[BradCustom]

In Crystal 2008 I'm having an issue with the formula listed above which calculates the goal Sales dollars for each work day from the start of the month to the current day. below is the current formula called DaysFromStart.

WhileReadingRecords;
Local DatetimeVar Start := dateserial(year(currentdate),month(currentdate),1);
Local DatetimeVar End := {Invoice_Header.Document_Date};
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and 
     Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days

It calculates the first day correctly but each day after the first is incorrect.

Here are the formulas that rely on DaysFromStart.

RoundDays

Round ({@DaysFromStart})

WorkDays

WhileReadingRecords;
Local DatetimeVar Start := dateserial(year(currentdate),month(currentdate),1);
Local DatetimeVar End := dateserial(year(currentdate),month(currentdate)+1,1)-1;
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and 
     Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days

GoalPerDay

1000000/{@WorkDays}

GoalDay

{@RoundDays}*{@GoalPerDay}

Thank you for your help it's very appreciated!!!

 

[kenhamady]

So one formula calculates the number of working days in the current calendar month, and another calculates the number of working days between the first of the calendar month and the transaction date. My first step would be to place these two formulas on the details band and confirm that they are both calculating the correct number of business days based on the transaction date of that record.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[BradCustom]

Ken,
I did what you suggested and the @workDays formula is correct. Everything is calculating correctly but the chart is showing incorrect values. Here is an example of the data.

Work Days this month= 22
DaysFromStart       GoalDay
1                  $45,454.55
2                  $90,909.09
3                  $136,363.64

The strange thing is how the Chart displays these numbers.
DaysFromStart     GoalDay
1                  $51,000.00 (approximate)
2                  $149,000.00 (approximate)
3                  $249,000.00 (approximate)

It appears there is something wrong with the Chart so I'm going to look there.

Thanks for your help.
Brad

 

[BradCustom]

Mystery solved!
I'm using a stacked graph and it's adding the DayInv total to the GoalDay total. Thats why the GoalDay total is so high and erratic.

Thanks,
Brad