Arrays - How many in a week

[suzey]

Dear All,

I would like to be able to count an item to see how many were open items within a particular week.

So, I have a start date and a finish date for each item, and I want to count the items for each week that falls between these two dates.

I have the following (which I copied from the forums) and thought I'd modified correctly, but unfortunately do not really understand what I am doing.

- - - - - -
whileprintingrecords;
datetimevar MyDate:={Datecreated};
numbervar Counter;
numbervar array weekCount;
redim preserve weekCount [53];
For Counter := 1 to datediff("d",{Datecreated},{@Comp Date}) do
(

weekCount[datepart ("ww",(MyDate)-1,2,2)]:=weekCount[datepart ("ww",(MyDate)-1,2,2)]+1;
MyDate := MyDate+1;
);
- - - - - - -

Can anyone point me in the right direction. This is the first time I've needed to use an array.

Thanks



Sue

 

[synapsevampire]

You shouldn't need an array.

Create a Running Total, and in the Evaluate->Use a Formula apply your formula, whatever it might be. I don't see the 2 dates here that would be compared...

Somthing like:

{table.date} in {?startdate} to {?enddate}

It's hard to know what you mean by a week, and Crystal does not use the ISO standard, so please post specifics.

Successful posts generally include techincal information:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[suzey]

Dear Synapsevampire,

Sorry for the lack of detail, frustration had set in!! I am using Crystal 8, and connecting to data on an SQL server.

What I'd like to do is find out the status for each item at a particular point ie: during week 24, how many items were complete, active, rejected, cancelled.

The status of the items can change during each week, so an item may have been rejected in week 23, then made active again in week 24 and cancelled in week 25.

So for each item, I'd like to do something like this :

When was the item active? :
item start date end date wk1 wk2 wk3 wk4 wk5
ABC 03/01/05 31/03/05 yes yes yes yes yes
DEF 10/01/05 14/01/05 no yes no no no
GHI 17/01/05 31/03/05 no no yes yes yes
TOTAL 1 2 2 2 2

Was the item rejected? :
item start date end date wk1 wk2 wk3 wk4 wk5
ABC 03/01/05 31/03/05 no no yes no no
DEF 10/01/05 14/01/05 no no no no no
GHI 17/01/05 31/03/05 no no yes no no
TOTAL 0 0 2 0 0

Was the item completed? :
item start date end date wk1 wk2 wk3 wk4 wk5
ABC 03/01/05 31/03/05 no no no no no
DEF 10/01/05 14/01/05 no yes no no no
GHI 17/01/05 31/03/05 no no no no yes
TOTAL 0 1 0 0 1

Then summarise the data
wk1 wk2 wk3 wk4 wk5
Active 1 2 2 2 2
Rejected 0 0 2 0 0
Completed 0 1 0 0 1

I have an audit trail in the database, so I have a record whenever an item changes status.

I know I can easily get the number completed, and rejected in a week, as I'm only looking at one date, but I was not sure how to find out when an item was active based on the start and finish dates.

Any help would be really appreciated. I think I've been looking at this too long now, and need someone elses perspective.



Sue