Skip to Content
0
Former Member
Jul 23, 2008 at 10:23 AM

Crosstab counter

14 Views

Dear all,

I am having strange issues with CrossTab. I have added Cross tab to calculate number of jobs per day.

Though the total number of jobs are coming up fine, with number of days coming up fine however, the jobs within those days are all messed up.

For example:

Date Range: 05/06/2008 - 10/06/2008

Order Date: 05/06/2008

Despatched: 04/07/2008

Order Date: 06/06/2008

Despatched: 10/06/2008

The crosstab shows that there has been two jobs shipped on 1 day however, it is not true because from 05/06/2008 to 10/06/2008, there has been only one job which was shipped!!! the other job was shipped on the 04/07/2008.

How come Cross tab is calculating jobs which were not even shipped within the date range?

Here is the formula:

Local DateVar Start := {order_header.date_entered};   // Starting Date
Local DateVar End := {order_header.act_despatch};  // Ending 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 - Hol;

Though Total jobs are 2 and but those two jobs are shipped in two different dates and not the same, why is cross tab is including future despatched orders in the same month?

Please help!!

Many thanks

Kind Regards

Jehanzeb