Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Back ward Projection Dates Post Reply Post New Topic
Author Message
JohnS.
Newbie
Newbie
Avatar

Joined: 08 Mar 2007
Location: United States
Online Status: Offline
Posts: 18
Quote JohnS. Replybullet Topic: Back ward Projection Dates
    Posted: 16 May 2007 at 8:33am
Hi all,
 
I am not sure of the best way to handle this. I have tried some different formulas in the editor but nothing sofar.
 
I have a report that i need to create that gives a backward projection of dates, which are cutoff points of when certain tasks need to be completed to keep on schedule.  I have one date now that i use to subtract so many days from for each cut off date (no problem there). My problem is that i need actual business days not including weekends. For example:
 
If my deadline date is May 30th and one of my critical dates is 12 days before that, it has to be 12 working days monday - friday. Right now my formula is simple (Cutoff date - 12) but that will not work for what i need. I formatted the date fields to include the day of the week. But i am not sure on how to write the formula for excluding weekends and still count back 12 working days. Any ideas would be great.
 
JohnS.
 


Edited by JohnS. - 16 May 2007 at 8:34am
IP IP Logged
JohnS.
Newbie
Newbie
Avatar

Joined: 08 Mar 2007
Location: United States
Online Status: Offline
Posts: 18
Quote JohnS. Replybullet Posted: 16 May 2007 at 12:49pm
I found the answer to what i needed. Problem solved
JohnS.
 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 16 May 2007 at 1:53pm
Can you enlighten us? I'm curious how you pulled it off.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
JohnS.
Newbie
Newbie
Avatar

Joined: 08 Mar 2007
Location: United States
Online Status: Offline
Posts: 18
Quote JohnS. Replybullet Posted: 16 May 2007 at 9:01pm
Hey Brian,
It wasn't to hard after a little thinking. The report is what we use to give projected dates that work off of one ending date. This is for each department to have the tasks completed on time. The only actual field on the report that is tied to the the sql database is my ending date. We have preset  so many days back from the ending date for each department. These are consecutive dates, so once one department is finished the project is moved to the next department and so on.
 
Here is what i did. I placed my ending date on the report as the last date in the row. Then i placed five unbound fields on the report representing each departments cut off date for completion. I used the formula editor to create the crystal syntax formula for each field.
 
Here is the code i used on the formula:
 
DateVar  EstDeliveryDate := {Project.ProjectedDeliveryDate};
DateVar EstCuttOffDate := {Project.ProjectedDeliveryDate} - 10;  //this could be any number you choose.
NumberVar ActualDays := DateDiff("d", EstDeliveryDate, EstCuttOffDate) - DateDiff("ww", EstDeliveryDate, EstCuttOffDate, crsaturday) - DateDiff("ww", EstDeliveryDate, EstCuttOffDate, crsunday);
{Project.ProjectedDeliveryDate} - 10 + NumberVar + 10
 
The last calculation looks odd but it works, I have run the report and checked it against an actual callendar and it comes out right everytime. I imagine there is probably room for improvement.
 
I placed this formula inside of each of my unbound date fields and just modified the actual number of days for each field going backwards adding so many more days to each number 10 -> 20 -> 26 -> 52 -> 58.
 
I will check in the morning to make sure of the exact wording, but this is the formula i used.
The only thing that i have not included yet is holidays which, we work all but Christmas so it really doesn't matter.
 
Hope this helps anyone who might need it.
 
 
 


Edited by JohnS. - 16 May 2007 at 9:11pm
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 16 May 2007 at 9:49pm
Thanks for posting the calculation. However, I really have no idea what that last calculation does. You subtract ten days then add it back? Hmmm... Also, where it says 'NumberVar', should it say 'ActualDays'?

A bit confused.


Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
JohnS.
Newbie
Newbie
Avatar

Joined: 08 Mar 2007
Location: United States
Online Status: Offline
Posts: 18
Quote JohnS. Replybullet Posted: 17 May 2007 at 3:55am
Hey Brian,
Here is the formula i copied this morning out of the editor.
I can imagine this can be refined more.
:
Datevar EstDeliveryDate := Date({ProjectPhase.EstimatedDeliveryDate});
Datevar DeliveryDate := Date({ProjectPhase.EstimatedDeliveryDate} - 52);
Numbervar DaysDiff := DateDiff("d",EstDeliveryDate,DeliveryDate)-
DateDiff("ww",EstDeliveryDate,DeliveryDate,crsaturday)-
DateDiff("ww",EstDeliveryDate,DeliveryDate,crsunday);
Numbervar DiffDays := 52 + DaysDiff + 52;
{ProjectPhase.EstimatedDeliveryDate}- DiffDays;
 
 
I know that i am adding back the same number of days to the same EstimatedDeliveryDate but that was the only way i could think of creating a startdate and an ending date. Then find how many weekend days were between those dates, Which will also be a negative number, then add the correct number of days that it takes to complete the task to that number.
 I basicly kept running the report every adjustment i made until i got the answer i needed.
 
Trust me i know it is confusing, but i was taking it one line at a time to see the effects of what i needed. Sheer excitement of getting it done brought to mind (if it ain't broke don't fix) poor excuse i know.
 
JohnS.
 


Edited by JohnS. - 17 May 2007 at 4:11am
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 17 May 2007 at 11:21am
ha. That's pretty good. Yes, if it aint's broke, don't fix it. I'm going to print this out and play around with it to get a better feel for it. Thanks!
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Robbo
Newbie
Newbie
Avatar

Joined: 14 Aug 2007
Location: United Kingdom
Online Status: Offline
Posts: 1
Quote Robbo Replybullet Posted: 14 Aug 2007 at 9:15am

I know that I am really late on this one but I came across this problem and noticed that the proposed solution does not give the correct result...

 

If I understand correctly the number being passed in to the formula (52 in this case) is supposed to represent the number of working days that the cut off date should represent in regards the final date.  However the solution is calculating the number of weekends to take in to consideration incorrectly

 
Firstly I will explain the code as I understand it
 
--- JohnS code ---

Numbervar DaysDiff := DateDiff("d",EstDeliveryDate,DeliveryDate)-
DateDiff("ww",EstDeliveryDate,DeliveryDate,crsaturday)-
DateDiff("ww",EstDeliveryDate,DeliveryDate,crsunday);

 

This formula gets confusing because the dates are being passed to datediff in the wrong order causing each DateDiff calculation to return a nagative value.  If the date parameters had been passed in the other way around the datediff could have been added rather than subtracted.

 

The Formula will break down like this:

DaysDiff := -52 - -8 - -8

DaysDiff := -52 +8 + 8

DaysDiff := -36

 

This leads to the second interesting calculation which is really only necessary because the original DateDiff yeilded negative numbers. 

--- JohnS Code ---

 

Numbervar DiffDays := 52 + DaysDiff + 52;
{ProjectPhase.EstimatedDeliveryDate}- DiffDays;

 

The first part of the calculation reverses the negation to find out how many additional days are actually being added i.e. 52 + -36 = 16

 

Then it gets added to the original 52 to calculate the actual total days to rewind i.e. 16 + 52 = 68; if the date values had been plugged in to the datediff function the other way around this calculation would have been unnecessary.

 

Now to the problem!
 
In this calculation 52 days yields 16 weekend days and if we take a date and minus 52 days from it this may indeed be correct (depending on which day of the week the end date falls on). However because we are thinking in working days this has to be wrong since there has to be at least 20 weekend days in 52 working days because for every 5 working days there must be a weekend.  There may also be an additional weekend to take in to consideration depending on which day the end date falls on.  In this case 5 goes in to 52 10 times with 2 days remaining so if the end date should fall on a Monday or a Tuesday then there will be an additional weekend to take in to consideration as well.

 

The following code solves the problem - The code 'as is' will show the workings.  If you would like to use the code set dtEnd to the desired end date value and set the WorkingDays appropriately then comment out or remove the output from the end and return the final datevar (dt) instead

 

//Replace the created date with the date you wish to use as the end date

datevar dtEnd := Date(2007,8,14);


numbervar WorkingDays := 52; //this could be taken directly from a report parameter or a db field

 

//calculate the number of full weeks
numbervar iCompleteWeeks := Truncate(WorkingDays / 5);


//To show working
numbervar iActualWeeks;

 

numbervar iAdditionalDays := Remainder(WorkingDays,5);

 

//get the day the end date falls on
numbervar iCurrent := DayOfWeek(dtEnd,crMonday) - 1;

 

//If there is a remainder and if the remainder is more than the current day then this will also cross over a
//weekend so add another Week
//This is a longer winded version to show the working could just alter iCompleteWeeks directly
if (iAdditionalDays > 0 and iAdditionalDays > iCurrent) then
    iActualWeeks := iCompleteWeeks + 1
else
    iActualWeeks := iCompleteWeeks;

 

//for every full week we add 2 days
numbervar iResult := WorkingDays + (iActualWeeks * 2);

 

//dt will contain the correct date.  This variable should be returned in the real code rather than the workings below.
DateVar dt := Date(DateAdd("d", -iResult,dtEnd));

//Return the following in the real code
//dt

 

//To see the workings leave this code active and ensure that the formula cell is able to grow.
"Working Days: " + totext(WorkingDays,0) + chr(13) +
"# of Weekends: " + totext(iCompleteWeeks,0) + chr(13) +
"Additional Days: " + totext(iAdditionalDays,0) + chr(13) +
"Day Of Week (End Date): " + totext(iCurrent,0) + chr(13) +
"# of actual weekends spanned: " + totext(iActualWeeks,0) + chr(13) +
"Total Days To Subtract: " + totext(iResult,0) + chr(13) +
"End Date: " + Totext(dtEnd) + chr(13) +
"Cut Off Date: " + totext(dt);

 

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.