Weekly Function ?

[ricolame]

Hi folks,

Need some expertise on this.

Have anyone done a report that actually breakdown by Weekly of the month.

Ie. 1st Week , 2nd Week , 3rd Week and 4th Week of the month?

I am trying to window our orders backlog into this 'window' period. I've done a similar one on monthly view ie for 3 consecutive months on my own custom function.

Any help on this ?

Thanks very much.

CR10




CR10 Win2k
SQLBase 7.6.1

 

[Madawc]

I've not actually done this, but here's a method that ought to work. Do a pair of formula fields, first @YearMonth:

Year({your.date}) & "/" & Month({your.date})

Second @WeekofYear:

DatePart("ww", {your.date})

Group by @YearMonth and then by @WeekofYear. This should get you the weeks in the year.

Actually labelling them Week 1 etc. can be done using different methods. Create a Running Total that adds once per @WeekofYear and resets for each @YearMonth. Use this to create a group header,

"Week " & #count

If you're not already familiar with Crystal totals, see FAQ767-6524.

An alternative would be a crosstab, which allows grouping by dates and more than one row-code. I've not tried this but you could explore it if you just want the totals and not the details.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ricolame]

Hi thanks.

Hm, i was thinking of the Week No of the Year to manipulate too.

I might need to be more elaborate on this. Sorry for the missed out details.

In fact i am extending on my current report that summarized order backlog figures into various period buckets ie (PastDued, Current Month, Next Month, Next 2nd Month, Future)

But i am now required to Show also, by extending on Current Month to few more period buckets ( 1st Week of Current Month, 2nd Week of Current Month, 3rd Week .. , 4th Week)

For the month bucket, what i basically did was wrote a custom function that determines which date-range bucket it will goes to.

ie determineXMonths(2007/06/02) will return me a P -past dued bucket.

And i worked on that for running totals on {@Backlog} for my various summary figures.


Determining which week of the month seems tougher than i thought.. maybe i shall not restrain, but go by which week no, and that the week no range i'm looking belongs to the current month period .

hm..think i roughly got the idea as i'm typing here..gd.shall try. tks.



CR10 Win2k
SQLBase 7.6.1

 

[lbass]

To get the week of the month, use:

datepart("ww",{table.date})-
datepart("ww",{table.date}-day({table.date})+1)+1

For the current month, you can substitute currentdate for {table.date}.

-LB