I am trying to create a 8 week rolling avg. Suppose I have some data and it's organized below...

Year Week JobCode Cases Hours
2007 52 101 24 5
2008 1 101 15 4
2008 2 101 51 9
2008 3 101 15 3
2008 4 101 84 12
2008 5 101 51 8
2008 6 101 15 4
2008 7 101 94 15
2008 8 101 15 1

The 8 week rolling average would be based on the previous 8 weeks from the current chosen week. So, in our sample that would be weeks 52 from 2007 and 1-7 in 2008, if I wanted to find the 8-week avg for Week #8.
I could just set this up to have Week 8-1 and 8-8, but that wouldn't work clearly. I have to find a way to be able to pass to the previous year. So, I added a Monday date to each week.

Year Week Date JobCode Cases Hours
2007 52 20071231 101 24 5
2008 1 20080107 101 15 4
2008 2 20080114 101 51 9
2008 3 20080128 101 15 3
2008 4 20080204 101 84 12
2008 5 20080211 101 51 8
2008 6 20080218 101 15 4
2008 7 20080225 101 94 15
2008 8 20080303 101 15 1

Now I can effectively use the Week 8 date to find my range. 20080303 - 56. 20080303 - 7. So, for Week 8 (Sorry if the math isn't right, but you get my drift) it start at 20071231 and ends at 20080225.
How could I build a query that would SUM the Cases & Hours in that date range?

This message has been edited. Last edited by: <Emily McAllister>,

Hi RMM,
How new are you to WebFOCUS?

That doesn't help... But about 3 months new.

Assuming you have your date logic correct, here is an example of summing and averaging over 4 weeks that I put together using the CAR file.

Hopefully this will get you in the right direction.

-* File: IBFS:/0-Dev/EDA/EDASERVE/APPPATH/matt/Procedure1.fex Created by WebFOCUS AppStudio
TABLE FILE CAR
PRINT MODEL NOPRINT
COMPUTE YEAR/I4= 2007;
COMPUTE WEEK/I4= 52;
COMPUTE JOBCODE/I4= 101;
COMPUTE CASES/I4= 24;
COMPUTE HOURS/I4= 5;
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS TPDAT FORMAT XFOCUS
END
-RUN

TABLE FILE CAR
PRINT MODEL NOPRINT
COMPUTE YEAR/I4= 2008;
COMPUTE WEEK/I4= 1;
COMPUTE JOBCODE/I4= 101;
COMPUTE CASES/I4= 15;
COMPUTE HOURS/I4= 4;
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS TPDAT1 FORMAT XFOCUS
END
-RUN

TABLE FILE CAR
PRINT MODEL NOPRINT
COMPUTE YEAR/I4= 2008;
COMPUTE WEEK/I4= 2;
COMPUTE JOBCODE/I4= 101;
COMPUTE CASES/I4= 51;
COMPUTE HOURS/I4= 9;
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS TPDAT2 FORMAT XFOCUS
END
-RUN

TABLE FILE CAR
PRINT MODEL NOPRINT
COMPUTE YEAR/I4= 2008;
COMPUTE WEEK/I4= 3;
COMPUTE JOBCODE/I4= 101;
COMPUTE CASES/I4= 15;
COMPUTE HOURS/I4= 3;
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS TPDAT3 FORMAT XFOCUS
END
-RUN

USE TPDAT   AS TPDAT
    TPDAT1  AS TPDAT
    TPDAT2  AS TPDAT
    TPDAT3  AS TPDAT

END

TABLE FILE TPDAT
PRINT YEAR
      WEEK
     JOBCODE
     CASES
     HOURS
ON TABLE HOLD AS TPDATTWO FORMAT XFOCUS
END
-RUN

DEFINE FILE TPDATTWO
CASE_CNT/I5  = IF CASES GT 0 THEN 1 ELSE 0 ;
HOURS_CNT/I5 = IF HOURS GT 0 THEN 1 ELSE 0 ;
END


TABLE FILE TPDATTWO
SUM  
     CASES
     HOURS
     COMPUTE AVG_CASE/P8.2 = CASES / CASE_CNT ;
     COMPUTE AVG_HOURS/P8.2 = HOURS / HOURS_CNT ;

END
-RUN



  

This is where I am at...

JOIN
LEFT_OUTER PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEYRN
AND PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEWKN IN PWCS_WJEMPWKF TO MULTIPLE
DW_DIM_TM.DW_DIM_TM.CALFSYR AND DW_DIM_TM.DW_DIM_TM.CALFSWK IN DW_DIM_TM TAG J0
AS J0
END

DEFINE FILE PWCS_WJEMPWKF
STARTS/YYMD = DATEADD(CALDTEI, 'D', -56);
ENDS/YYMD = DATEADD(CALDTEI, 'D', -7);
END

TABLE FILE PWCS_WJEMPWKF
SUM
PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEHRST
PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECSET
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEYRN
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEWKN
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECDE
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECVWDS
BY LOWEST J0.DW_DIM_TM.CALDTEI
BY STARTS
BY ENDS
WHERE J0.DW_DIM_TM.CALDAYW EQ 1;
WHERE PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECDE CONTAINS '101' OR '201' OR '203' OR '217' OR '209' OR '501' OR '509';
WHERE PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEWH EQ ' 1' OR ' 7';
WHERE PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEYRN LE '2007';
END

Running it gives me this...

Year Week JobCode Job Description CALDTEI STARTS ENDS HoursWorkedWk CasesPickedWk
2007 39 201 PULL A/B NITES 2007/09/24 2007/07/30 2007/09/17 1685.27 155235
203 PULL C NITES 2007/09/24 2007/07/30 2007/09/17 554.73 72584
40 201 PULL A/B NITES 2007/10/01 2007/08/06 2007/09/24 1621.41 148685
203 PULL C NITES 2007/10/01 2007/08/06 2007/09/24 512.29 64904
41 201 PULL A/B NITES 2007/10/08 2007/08/13 2007/10/01 1824.69 164261
203 PULL C NITES 2007/10/08 2007/08/13 2007/10/01 599.74 75623
42 201 PULL A/B NITES 2007/10/15 2007/08/20 2007/10/08 1877.37 161407
203 PULL C NITES 2007/10/15 2007/08/20 2007/10/08 573.83 77060
43 201 PULL A/B NITES 2007/10/22 2007/08/27 2007/10/15 2180.31 169755
203 PULL C NITES 2007/10/22 2007/08/27 2007/10/15 610.82 72208
44 201 PULL A/B NITES 2007/10/29 2007/09/03 2007/10/22 2131.05 160404
203 PULL C NITES 2007/10/29 2007/09/03 2007/10/22 546.49 64629
45 201 PULL A/B NITES 2007/11/05 2007/09/10 2007/10/29 2445.58 165426
203 PULL C NITES 2007/11/05 2007/09/10 2007/10/29 778.19 93454
46 101 PULL A/B DAYS 2007/11/12 2007/09/17 2007/11/05 .71 0
201 PULL A/B NITES 2007/11/12 2007/09/17 2007/11/05 3391.49 189048
203 PULL C NITES 2007/11/12 2007/09/17 2007/11/05 927.84 97807
47 201 PULL A/B NITES 2007/11/19 2007/09/24 2007/11/12 2088.44 135537
203 PULL C NITES 2007/11/19 2007/09/24 2007/11/12 723.99 84911
48 201 PULL A/B NITES 2007/11/26 2007/10/01 2007/11/19 2245.30 142581
203 PULL C NITES 2007/11/26 2007/10/01 2007/11/19 743.18 76030
49 201 PULL A/B NITES 2007/12/03 2007/10/08 2007/11/26 3366.22 152292
203 PULL C NITES 2007/12/03 2007/10/08 2007/11/26 783.74 89672
50 101 PULL A/B DAYS 2007/12/10 2007/10/15 2007/12/03 14.56 1784
201 PULL A/B NITES 2007/12/10 2007/10/15 2007/12/03 2770.92 173269
203 PULL C NITES 2007/12/10 2007/10/15 2007/12/03 818.23 96401
51 101 PULL A/B DAYS 2007/12/17 2007/10/22 2007/12/10 4.26 529
201 PULL A/B NITES 2007/12/17 2007/10/22 2007/12/10 2563.36 198177
203 PULL C NITES 2007/12/17 2007/10/22 2007/12/10 804.44 100249
52 101 PULL A/B DAYS 2007/12/23 2007/10/28 2007/12/16 41.81 1799
201 PULL A/B NITES 2007/12/23 2007/10/28 2007/12/16 1632.17 128821
203 PULL C NITES 2007/12/23 2007/10/28 2007/12/16 552.92 64199

...Tons more data after the sample size.

In TSQL, I could do a SELF JOIN and get this solved, but in WebFOCUS, I'm stumped. I tried everything for 4 days. So I hope you can understand my frustration with this.

It's hard to even read your posted code and output, when it's rendered in a proportional font. It should be wrapped in code tags (use the < / > icon).


Here's how I would approach it:

* DEFINE (or otherwise generate) a week-number field
* hold, in format Focus:
the measure for which you want moving averages
BY period, BY nominal date, BY the grouping key (jobcode) you need for output. index the grouping key

* construct a join structure of that hold file with itself. tag the left (join-from) side as C (for current), and the right as H (history). -- use the "conditional join" syntax, with a predicate that joins on the grouping key (or keys), and results in a cartesian product with respect to the time dimension.

* you can now summarize the eight most recent weeks with respect to a given week:

sum AVE.H.measure
by C.grouping_key
by C.date
where (C.week - H.week) from 0 to 7 ;

RMM, I wonder if this is the missing element. Your chosen week seems to me like it would be a variable, I'll call it &CHOSEN_WEEK. Eventually it might receive its value from a web page, for now, its value can be defaulted right in the procedure. Based on the code you provided, I could look something like this:

 
-DEFAULT &CHOSEN_WEEK = '20080225';
-SET     &STARTS      = AYMD(&CHOSEN_WEEK, -56, 'I8YYMD'); 
-SET     &ENDS        = AYMD(&CHOSEN_WEEK,  -7, 'I8YYMD');

JOIN
LEFT_OUTER PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEYRN
AND PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEWKN IN PWCS_WJEMPWKF TO MULTIPLE
DW_DIM_TM.DW_DIM_TM.CALFSYR AND DW_DIM_TM.DW_DIM_TM.CALFSWK IN DW_DIM_TM TAG J0
AS J0
END

TABLE FILE PWCS_WJEMPWKF
SUM 
PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEHRST
PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECSET
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEYRN
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEWKN
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECDE
BY LOWEST PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECVWDS
BY LOWEST J0.DW_DIM_TM.CALDTEI
WHERE J0.DW_DIM_TM.CALDAYW EQ 1;
WHERE PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJECDE CONTAINS '101' OR '201' OR '203' OR '217' OR '209' OR '501' OR '509';
WHERE PWCS_WJEMPWKF.PWCS_WJEMPWKF.WJEWH EQ ' 1' OR ' 7';
WHERE J0.DW_DIM_TM.CALDTEI GE '&STARTS'
WHERE J0.DW_DIM_TM.CALDTEI LE '&ENDS'
END