FrankTWall
Technical User
Hi All,
CRXI, SQL
I "borrowed" this code off the , I think, FAQ's of this forum. Works a treat. Now I need to generate an average number of days by week and month (2 groups in my report)
The results from the formula , shown below, are not available i.e. do not appear as report fields, when I go to automatically calculate an average of the value. Is there a way I can either modify the report to calculate average days by week / month or do I need to create a new formula??? Ideas anyone?
Thanks
Frank
//Use the following formula, replacing the items in Bold with your start/end date and the items in Italics with your holidays........
WhilePrintingRecords;
//Set the values of Start Date and End Date
DateTimeVar StartDate := {uvw_SOAR_SALESAT.PRJ_Date_CustomerSign};
DateTimeVar EndDate := {uvw_SOAR_SALESAT.CALC_ProvAt};
//Find out the difference in days and subtract the weekends
NumberVar DaysDiff := DateDiff("d",StartDate,EndDate) -
DateDiff("ww",StartDate,EndDate,crsaturday) -
DateDiff("ww",StartDate,EndDate,crsunday);
//Create an array of Holiday dates
Local DateVar Array Holidays := MakeArray(
//Closure 07/08
Date(2007,12,24),
Date(2007,12,27),
Date(2007,12,28),
Date(2007,12,31),
Date(2008,01,02),
Date(2008,01,03),
Date(2008,01,04),
//New Years
Date(2006,01,01),
Date(2007,01,01),
Date(2008,01,01),
Date(2009,01,01),
Date(2010,01,01),
Date(2011,01,01),
//Aust Day
Date(2006,01,26),
Date(2007,01,26),
Date(2008,01,26),
Date(2009,01,26),
Date(2010,01,26),
Date(2011,01,26),
//etc
);
//Loop through the array checking if each holiday is within the dates
Numbervar Counter := 0;
While UBound(Holidays) <> Counter do
(Counter := Counter + 1;
if Not(dayofweek(Holidays[Counter]) in [1,7]) and
Holidays[Counter] in StartDate to EndDate then DaysDiff := DaysDiff -1
;
//Display result to 0 decimal places and no thousand separator
tonumber(DaysDiff);
CRXI, SQL
I "borrowed" this code off the , I think, FAQ's of this forum. Works a treat. Now I need to generate an average number of days by week and month (2 groups in my report)
The results from the formula , shown below, are not available i.e. do not appear as report fields, when I go to automatically calculate an average of the value. Is there a way I can either modify the report to calculate average days by week / month or do I need to create a new formula??? Ideas anyone?
Thanks
Frank
//Use the following formula, replacing the items in Bold with your start/end date and the items in Italics with your holidays........
WhilePrintingRecords;
//Set the values of Start Date and End Date
DateTimeVar StartDate := {uvw_SOAR_SALESAT.PRJ_Date_CustomerSign};
DateTimeVar EndDate := {uvw_SOAR_SALESAT.CALC_ProvAt};
//Find out the difference in days and subtract the weekends
NumberVar DaysDiff := DateDiff("d",StartDate,EndDate) -
DateDiff("ww",StartDate,EndDate,crsaturday) -
DateDiff("ww",StartDate,EndDate,crsunday);
//Create an array of Holiday dates
Local DateVar Array Holidays := MakeArray(
//Closure 07/08
Date(2007,12,24),
Date(2007,12,27),
Date(2007,12,28),
Date(2007,12,31),
Date(2008,01,02),
Date(2008,01,03),
Date(2008,01,04),
//New Years
Date(2006,01,01),
Date(2007,01,01),
Date(2008,01,01),
Date(2009,01,01),
Date(2010,01,01),
Date(2011,01,01),
//Aust Day
Date(2006,01,26),
Date(2007,01,26),
Date(2008,01,26),
Date(2009,01,26),
Date(2010,01,26),
Date(2011,01,26),
//etc
);
//Loop through the array checking if each holiday is within the dates
Numbervar Counter := 0;
While UBound(Holidays) <> Counter do
(Counter := Counter + 1;
if Not(dayofweek(Holidays[Counter]) in [1,7]) and
Holidays[Counter] in StartDate to EndDate then DaysDiff := DaysDiff -1
;//Display result to 0 decimal places and no thousand separator
tonumber(DaysDiff);