Sales Daily YTD Avg

[Durango122]

Hi Folks
ms sql
cr 9.0
i have a report that has a {?End-date} that i used as my parameter.
i need some help on how to calculated Sales Daily YTD Avg
keeping in mind they want working days with are monday to friday and holidays.

my date field is
{Transaction_History.Posting_Date}
to calculated my daily sales i used this formula
----------------------------------
@Daily_Sales

if {TRANSACTION_HISTORY.POSTING_DATE} <= {?End_Date}
and
{TRANSACTION_HISTORY.TRADE_CLASS} = "S"
then
round({TRANSACTION_HISTORY.GROSS_AMOUNT})
else
0
---------------------------------------
the holidays days are
2007 Holiday Schedule

New Year's Day Monday, January 1
Martin Luther King, Jr. Day Monday, January 15
President's Day Monday, February 19
Good Friday Friday, April 6
Memorial Day Monday, May 28
Independence Day Wednesday, July 4
Labor Day Monday, September 3
Thanksgiving Day Thursday, November 22
Christmas Day Tuesday, December 25

Can someone help me out on this one
and if possible some example on the coding would be appreciated

Thanks




Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[Durango122]

ops forgot
all has to be from the {?End-date} paramter the user select
Thanks


Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[Durango122]

Hi All
I check Ken formula and modified it
not sure if this is right way.
Mayby someone can check and see if i'm on the right track

-----------------------------------------------------------
The number of "Work Days" between two dates:
Calculating the number of calendar days between two dates is fairly simple. However, if you need to calculate the number of work days, it gets a bit more difficult. Not only do you have to account for weekends, in many instances you also have to account for holidays, which change each year.
The formula below does both. It requires that you enter your list of holiday dates in a separate formula. The list should not include holidays that will occur on weekends since they are already skipped by the formula. However if you do include a weekend holiday the formula will ignore it. The holiday list can include dates several years in advance. There are even ways to have the formula generate a warning when the holiday list is about to expire (ask me about this). To use this formula, simply copy it into your report and put your field names in on the second and third lines in place of the field names I have used. If your fields are DateTime fields you should put your fields within the DATE() function to convert them to dates. If you get an error you can try changing the first line to WhilePrintingRecords.

If you want to include holidays you put your holiday list in the second formula and then place this formula in the report header. Then suppress the formula or the section.
If you need to do business hours, see Formula #13.
To add a number of business days to a date use Formula #17.

NOTE - this formula counts both the starting date AND the ending date, if both are work days. So if you start on Monday and end on Tuesday, this counts as 2 days.


//Main formula
WhileReadingRecords;
Local DateVar Start := Date(year({?End_Date},1,1)); // place your Starting Date here
Local DateVar End := {?End_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) + 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 ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

//--------------------------------------------------------

// 2007 Holiday list
//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2007,1,1),
Date (2007,1,15),
Date (2007,2,19),
Date (2007,4,6),
Date (2007,5,28),
Date (2007,7,4),
Date (2007,9,3),
Date (2007,11,22),
Date (2007,12,25)
];
0
//---------------------------------------------------------
my work days are monday to friday not sure if i got the right info in the formula
all i need is the # of days then i can get the avg by using the sum of Daily Sales / by # of day return by this formula above
NOTE not sure about the holiday formula
i do want the holiday s to be check if it's on a monday to friday. if so substract it from # of days return

thanks



Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[Durango122]

hi All
Well i got it to works
thanks

Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[Durango122]

Thanks to Ken formula

Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40