Count of publishing days between two week ending dates. 1

[Liamster]

Use CR 8.0.

I need to find a way of auto - calculating the number of publishing days between two week ending dates that are each entered as a parameter at run time.

Week ending dates end every Sunday, begin Monday. One would look like 20050220.

Currently the users are having to manually enter the total # of publishing days as a parameter. This number is used in a formula to calculate the average daily sale for given customer segments on the report. As you can imagine, leaving this up to the users, we are getting different average daily sale figures. Each user has the ability to affect the average (up or down) by adjusting the total # of pub days parameter at run time.

Publishing days never fall on a Saturday or Sunday. Nor do they fall on any of the six major US holidays (New Years Day, Memorial Day, Fourth of July, Labor Day, Thanksgiving & Christmas).

We do utilize database fields such as the following, but I am at a loss on how to get a total or sum for the pub days between given dates ranges (week ending dates).

The available database fields that seem to make sense to me:

{Sales Data.Publishing Days for the Week}>been trying to work with this one the most, summing the number given from details band, but the number is always way to large to be right.
{Sales Data.Cumulative Publishing Days for the Period}
{Sales Data.Week Ending Date}

Let me know if you need more information. Thanks for your help!

 

[lbass]

Try using Ken Hamady's formulas for the number of work days between two dates. Go to faq767-995 and then see his first formula example.

-LB

 

[Liamster]

Thanks LB for the heads up, I had looked at this FAQ, but was uncertain it would work for my situation.

So, I am getting "A Date Is Required Here" error when I put the parameter {?DateFrom} into Ken's formula below.

WhileReadingRecords;
Local DateVar Start := {?DateFrom};
Local DateVar End := {?DateTo};
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 Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

I am new to this, so not sure what to do with the error message. Tried parens, did not work.

Thanks.

 

[lbass]

First, I think you should convert your date field to a date datatype by using something like:

date(val(left({table.datestring},4)),val(mid({table.datestring},5,2)),val(right({table.datestring}),2)))

Then change the datatype of your parameters to date, not string. Then apply Ken's formula.

Alternatively, you could use the above formula to convert the datatype of the parameter to match the variable datatype in Ken's formula.

-LB