Creating Monthly/Weekly Buckets

[GirlNet]

Hi All,

I have the below code that is somewhat confusing to me - being new to the 4GL world. I'd like to get and explanation and also ideas of how to bucket this up from monthly to weekly. I rem'd some of the monthly code out and substituted weekly. I tried to cut it down to make it more manageable, so I may have left something out. Using Progress 9.1E. Any suggestions would be greatly appreciated.

def var v-this-month   as   int        no-undo.
def var v-this-date    as   date       no-undo.
def var net_req           as   dec          no-undo.
 
def temp-table result-temp no-undo
   field result-out   as   dec           format "->>,>>>,>>9" extent 12.
 
if v-begin-date eq ? or day(v-begin-date) ne 1 then do:
   /*v-begin-date = date(month(v-begin-date),1,year(v-begin-date)).*/
    v-begin-date =  today - weekday(today).
    display v-begin-date with frame a.
end.
 
v-end-date = v-begin-date + 65.
 
for each schd_det no-lock
                      where schd_type    eq sch_type
                      and   schd_nbr     eq sch_nbr
                      and   schd_line    eq sch_line
                      and   schd_rlse_id eq sch_rlse_id
                      and   schd_date    ge v-begin-date
                      and   schd_date    le v-end-date:
           assign
                net_req = cum_net_req - prior_cum_net_req
end.
 
      v-this-month = month(schd_date).
     [COLOR=darkred][B] v-this-month = (13 - month(v-begin-date)) + v-this-month. <-- What does this do?
[/B][/COLOR]      [COLOR=darkred]if v-this-month gt 12 then v-this-month = v-this-month - 12.[/COLOR]
              [COLOR=darkred][B]result-out[v-this-month] = result-out[v-this-month] + net_req. <--Calcuates net_reg by month now.  Need weekly.
[/B][/COLOR]          end.

 

[GirlNet]

Does anyone have an example of grouping data by week? Please . . .

 

[vinod_home]

v-begin-date = today - weekday(today). (so its first day of this week)

v-this-month = month(schd_date)
v-this-month = (13 - month(v-begin-date)) + v-this-month. <-- What does this do?
- this calcuates if the month(schd_date) is more than a year from the begin-date.

Example:
v-begin-date = 22-02-2010 and schd_date = 03-01-2011
If you want the number of months between the dates, you cannot use
month(schd_date) - month(v-begin-date). That would be 1 - 2 = -1 .

 

[GirlNet]

Thanks vinod_home for the info! That makes sense now. Still looking for an example on grouping data by week though. Let's say v-begin-date is 2/1 and v-end-date is 2/28. How would I group the below by week? So I would have a 4 week column report.


for each schd_det no-lock
where schd_type eq sch_type
and schd_nbr eq sch_nbr
and schd_line eq sch_line
and schd_rlse_id eq sch_rlse_id
and schd_date ge v-begin-date
and schd_date le v-end-date:
assign
net_req = weeklyBucket
end.

 

[vinod_home]

You would first make buckets.
1. find the sunday (or monday whichever is your week's first day) using ( v-begin-date - weekday(v-begin-date))
2. Add 7 till you are greater than v-end-date.
Now you should have all your start and end dates for the week buckets.
3. Scan through schd_det and see if the schd_date falls between the dates and add it into the appropriate week bucket.

HTH

 

[GirlNet]

I've been trying . . . . working with this all week now. I can get the header info to display by week, but I'm not getting the sum by week. Here's what I have - can someone review and tell me where I'm making the errors. This code is VERY slimmed down to simplicity (I took out form, display, and variables) - let me know if I'm missing extremely important.

/*start with beginning of current month*/
v-this-date = date(month(v-begin-date),1,year(v-begin-date)).

/*loop through week dates - assign to head-label*/
do i = 1 to 12:
head-label = " " + string(v-this-date).
v-end-date = v-this-date + 14.
v-this-date = v-this-date + 7.
end.

/*query the info*/
for each schd_det no-lock
where schd_date ge v-begin-date
and schd_date le v-end-date:
/*assign values*/
assign
cum_net_req = max(schd_cum_qty - sod_cum_qty[1],0)
net_req = cum_net_req - prior_cum_net_req
prior_cum_net_req = cum_net_req.

/*assign values to grouped columns*/
result-out = result-out + net_req.

/*displays everything*/
display
head-label[1 for 12] /*This correctly display each week.*/
with frame head.
display
result-out[1 for 12]/*This has the same total for each week*/
with down frame b.
down with frame b.

 

[vinod_home]

Your query is outside the do loop. Just move the "end" statement on line 9 to the end. That way you have the "for each" and "display" for each week. Also make sure that you are using the correct date variables in your for each statement. Its easier if you use an example and go through your logic on paper.

[SIZE=2]
/*start with beginning of current month*/[/SIZE]
[SIZE=2]v-this-date =  date(month(v-begin-date),1,year(v-begin-date)).[/SIZE]
 
[SIZE=2]/*loop through week dates - assign to head-label*/[/SIZE]
[SIZE=2]do i = 1 to 12: [/SIZE]
[SIZE=2]head-label[i] = " " + string(v-this-date). [/SIZE]
[SIZE=2]v-end-date = v-this-date + 14.[/SIZE]
[SIZE=2]v-this-date = v-this-date + 7. [/SIZE]
[SIZE=2]end.   ------> Loop finishes creating the header and v-end-date is the last week. (line 9)

[/SIZE] [SIZE=2]/*query the info*/[/SIZE]
[SIZE=2]for each schd_det no-lock[/SIZE]
[SIZE=2]where schd_date ge v-begin-date[/SIZE]  ---> always the first day of the month. may need to be v-this-date
[SIZE=2]and schd_date le v-end-date:[/SIZE]  ------> v-end-date is the last week so you will only get one total 
[SIZE=2]/*assign values*/[/SIZE]
[SIZE=2]assign[/SIZE]
[SIZE=2]cum_net_req = max(schd_cum_qty - sod_cum_qty[1],0)[/SIZE]
[SIZE=2]net_req = cum_net_req - prior_cum_net_req[/SIZE]
[SIZE=2]prior_cum_net_req = cum_net_req.[/SIZE]
 
[SIZE=2]/*assign values to grouped columns*/[/SIZE]
[SIZE=2]result-out[i] = result-out[i] + net_req.[/SIZE]
 
[SIZE=2]/*displays everything*/ [/SIZE]
[SIZE=2]display [/SIZE]
[SIZE=2]head-label[1 for 12] [B]/*This correctly display each  week.*/[/B][/SIZE]
[SIZE=2]with frame head.[/SIZE]
[SIZE=2]display[/SIZE]
[SIZE=2]result-out[1 for 12[B]]/*This has the same total for each  week*/[/B][/SIZE]
[SIZE=2]with down frame b.[/SIZE]
[SIZE=2]down with frame b.

[/SIZE]

 

[GirlNet]

Thank you!!!! That really helped to point me in the right direction.