Formula - production report outputs by week over months

[typhoid]

I am compiling a number of production reports, those that are working on data"now" are fine, however I am having difficulty in compiling a report which shows trends. That is I am looking to trend data for the last 3months by week. >Sum work orders opened by week for last 3 month period
>Sum work orders closed by week for last 3 months
>Sum of currently open work orders by week for last 3 months.

I'm a newby to crystal, using v7 at present hope this q wasnt too basic?

regards

John

 

[Ngolem]

no...it isn't trivial....

You don't describe your data tables at all. Do you have a field called "week"....(he said hopefully.... )

If you do it is fairly simple...if you don't it is a bit more complicated since you must decide What constitutes a "week" (ie is it a calendar week, business week, every 7 days from Jan 1...whatever)

Ok....you have a field called "week" I will assume. Then you just group on this value for the first Group

Many here use running totals but I favour manual totals personally since I understand them better....personal prteference...especially when using cr7

So You do a 3 formula sum

@initialize ( Suppressed in Group weekly header)

WhilePrintingRecords;

if not inrepeatedGroupheader then
(
numberVar TotalOpenedWeekly := 0;
numberVar TotalClosedWeekly := 0;
numberVar TotalCurrentlyOpenedWeekly := 0;
);


@Calc (suppressed in detail section)

WhilePrintingRecords;

numberVar TotalOpenedWeekly ;
numberVar TotalClosedWeekly ;
numberVar TotalCurrentlyOpenedWeekly;

If (put criteria defining open order) then
TotalOpenedWeekly := TotalOpenedWeekly + 1;

If (put criteria defining Closed order) then
TotalClosedWeekly := TotalClosedWeekly + 1;

If (put criteria defining Currently open order) then
TotalCurrentlyOpenedWeekly := TotalCurrentlyOpenedWeekly + 1;

now in the "week" group footer you display the results...either in one formula or 3 separate ones...I'll do one

@DisplayTotalOpened
WhilePrintingRecords;

TotalOpenedWeekly ;

That is it Jim Broadbent

 

[Ngolem]

of course you would set a formula in your record selection to grab data from 3 months prior in the database.

This can be tricky if the data goes back to the previous year since your "weeks" will be out of sync after you cross into January.

to prevent this you might join the year and week in a formula and group on that rather than by week alone

eg 2002_50
2002_51
2002_52
2003_01
2003_02

This will sort properly

Instead of "Week" as the group use this

@Group1

totext(Year({Table.datefield},0,"","&quot + "_" + totext({Table.week},0,"","&quot ;

So...see your problem isn't trivial Jim Broadbent

 

[typhoid]

Jim, thanks for your previous reply
I'll start working down that track soon
background info is:
Work order has date field for raised
Work order has status date feild for a change of status
no weekly total feild exists.

cheers

John

 

[lyanch]

Can you just use Crystals grouping and group by date with weekly intervals to get your week totals?

Lisa