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.
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>,
WebFOCUS 8 Windows, All Outputs
May 17, 2016, 09:07 AM
Msondra
Hi RMM, How new are you to WebFOCUS?
WebFOCUS 8.1.05 Windows, All Outputs
May 17, 2016, 09:24 AM
RMM
That doesn't help... But about 3 months new.
WebFOCUS 8 Windows, All Outputs
May 17, 2016, 10:46 AM
MattC
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
WebFOCUS 8.1.05
May 17, 2016, 01:56 PM
RMM
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
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
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.
WebFOCUS 8 Windows, All Outputs
May 17, 2016, 02:48 PM
j.gross
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 ;
- Jack Gross WF through 8.1.05
May 17, 2016, 08:32 PM
dbeagan
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