Hello,

We are new to the WebFocus community, and although I've done some searching in the forums, I haven't seen any clear description of how to approach my issue.

Specifically, I am trying to create a report that has multiple rows of computed metrics, across columns of varying timeframes.

For example, I might have 3 rows: Total Weight, Total Miles, Total Cost - which are computed sums from my master file.

I would like to report on these values for the previous 3 weeks, the same week last year, and a 52 week running total.

I can easily get the weeks with an across stmt, but am unsure of how to create a structure with different timeframes.

Any suggestions?

Thanks much,
Andy Bass

This message has been edited. Last edited by: Andy Bass,

Here is a very simple example starting point. Hope this helps.

TABLE FILE CAR
SUM
RETAIL_COST OVER
DEALER_COST
ACROSS COUNTRY
END

This is an example program that extracts data for different time periods and then displays them all in one report:

SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
SET ASNAMES    = ON
-RUN

DEFINE FILE CENTORD
ORDER_YEAR/A4YY = ORDER_DATE;
ORDER_MT1/A6YYM = ORDER_DATE;
ORDER_MONTH/A7 = EDIT(ORDER_MT1,'9999/99');

ORDER_DT1/A8YYMD = ORDER_DATE;
ORDER_DT2/HYYMD  = HINPUT(8, ORDER_DT1, 8, 'HYYMD');
ORDER_WK1/I2     = HPART(ORDER_DT2, 'WEEK', 'I2');
ORDER_WEEK/A7    = ORDER_YEAR | ' ' | EDIT(ORDER_WK1);
END
-RUN

TABLE FILE CENTORD
SUM
ORDER_YEAR/A10 AS PERIOD
COMPUTE PERIOD_SORT/A1 = '1';
COMPUTE PERIOD_TYPE/A5 = 'YEAR';
QUANTITY
LINEPRICE

BY PLANT
BY ORDER_YEAR NOPRINT

ON TABLE HOLD AS H001

WHERE ORDER_YEAR FROM '2000' TO '2001'
END
-RUN

TABLE FILE CENTORD
SUM
ORDER_MONTH/A10 AS PERIOD
COMPUTE PERIOD_SORT/A1 = '2';
COMPUTE PERIOD_TYPE/A5 = 'MONTH';
QUANTITY
LINEPRICE

BY PLANT
BY ORDER_MONTH NOPRINT

ON TABLE HOLD AS H002

WHERE ORDER_MONTH FROM '200011' TO '200102'
END
-RUN

TABLE FILE CENTORD
SUM
ORDER_WEEK/A10 AS PERIOD
COMPUTE PERIOD_SORT/A1 = '3';
COMPUTE PERIOD_TYPE/A5 = 'WEEK';
QUANTITY
LINEPRICE

BY PLANT
BY ORDER_WEEK NOPRINT

ON TABLE HOLD AS H003

WHERE ORDER_WEEK FROM '2001 08' TO '2001 12'
END
-RUN

TABLE FILE H001
SUM
QUANTITY
LINEPRICE

BY PLANT

ACROSS PERIOD_SORT  NOPRINT
ACROSS PERIOD_TYPE  AS ''
ACROSS PERIOD       AS ''

MORE
FILE H002
MORE
FILE H003
END
-RUN

Andy, here is a program/technique that I developed for one of my users to do something similar to what you want to do. It is done using the demo file CENTURYSALES. He was able to convert it to his file with few problems. It uses a technique called alternate master with an OCCURS clause.

-* File rolling_12months.fex
-DEFAULT &ENDYYM=200606;
-SET &BEGYYM=AYM(&ENDYYM,-12,'I6YYM');
-SET &PREVYR=EDIT(&BEGYYM,'9999');
-SET &CURRYR=EDIT(&ENDYYM,'9999');
-SET &ENDOFYR=&PREVYR||'12';
-SET &BEGOFYR=&CURRYR||'01';
-SET &PREVMOCNT=YM(&BEGYYM,&ENDOFYR,'I3')+1;
-SET &CURRMOCNT=YM(&BEGOFYR,&ENDYYM,'I3')+1;
-SET &BEGDT=&BEGYYM||'01';
-SET &NXTYYM=AYM(&ENDYYM,1,'I6YYM');
-SET &NXTDT=&NXTYYM||'01';
-SET &ENDDT=AYMD(&NXTDT,-1,'I8YYMD');
-TYPE &BEGYYM &BEGDT &NXTDT &ENDDT &PREVYR &CURRYR &PREVMOCNT &CURRMOCNT
-*-EXIT
DEFINE FILE CENTURYSALES
ROLLRTN/P8C=IF YYEARMONTH EQ &BEGYYM THEN 0 ELSE RETURN;
ROLLPRF/D20=IF YYEARMONTH EQ &BEGYYM THEN 0 ELSE PROFIT;
CURRAVGRTN/P8C=IF YEAR EQ &CURRYR THEN RETURN ELSE 0;
PREVAVGRTN/P8C=IF YEAR EQ &PREVYR THEN RETURN ELSE 0;
CURRAVGPRF/P20=IF YEAR EQ &CURRYR THEN PROFIT ELSE 0;
PREVAVGPRF/P20=IF YEAR EQ &PREVYR THEN PROFIT ELSE 0;
-SET &LOOPYYM=&BEGYYM;
-REPEAT ENDLOOP FOR &I FROM 1 TO 13 STEP 1
-*-TYPE &I
-* COMPUTE BUCKETS FOR EACH MONTH
RTN&I/P8C=IF YYEARMONTH EQ &LOOPYYM THEN RETURN ELSE 0;
PRF&I/P20=IF YYEARMONTH EQ &LOOPYYM THEN PROFIT ELSE 0;
-SET &LOOPYYM=AYM(&LOOPYYM,1,'I6YYM');
-ENDLOOP
END
-*-EXIT
TABLE FILE CENTURYSALES
SUM
-SET &LOOPYYM=&BEGYYM;
-REPEAT ENDPRNT FOR &I FROM 1 TO 13 STEP 1
-*-TYPE &I
-* PRINT BUCKETS FOR EACH MONTH
-SET &MONTH=EDIT(&LOOPYYM,'$$$$99');
COMPUTE LOOPMONTH&I/A2='&MONTH'; NOPRINT
COMPUTE COLHDR&I/A20=DECODE LOOPMONTH&I('01' 'January' '02' 'February' '03' 'March'
                                        '04' 'April'   '05' 'May'      '06' 'June'
                                        '07' 'July'    '08' 'August'   '09' 'September'
                                        '10' 'October' '11' 'November' '12' 'December');
RTN&I/P8C
PRF&I/P20
-SET &LOOPYYM=AYM(&LOOPYYM,1,'I6YYM');
-ENDPRNT
COMPUTE COLHDR14/A20='12 Mo. Rolling Avg';
ROLLRTN NOPRINT
COMPUTE RTNAVG/P8C=ROLLRTN/12;
ROLLPRF NOPRINT
COMPUTE PRFAVG/P20=ROLLPRF/12;
COMPUTE COLHDR15/A20='&CURRYR YTD Avg';
CURRAVGRTN NOPRINT
COMPUTE CURRRTNAVG/P8C=CURRAVGRTN/&CURRMOCNT;
CURRAVGPRF NOPRINT
COMPUTE CURRPRFAVG/P20=CURRAVGPRF/&CURRMOCNT;
COMPUTE COLHDR15/A20='&PREVYR Avg';
PREVAVGRTN NOPRINT
COMPUTE PREVRTNAVG/P8C=PREVAVGRTN/&PREVMOCNT;
PREVAVGPRF NOPRINT
COMPUTE PREVPRFAVG/P20=PREVAVGPRF/&PREVMOCNT;
BY PLANTNAME
WHERE ORDERDATE GE &BEGDT AND ORDERDATE LE &ENDDT;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT ALPHA
END
-*? HOLD HOLD
-*-EXIT
-RUN
APP FI ALTMAS DISK altmas.mas
-RUN
-WRITE ALTMAS FILENAME=HOLD,SUFFIX=FIX
-WRITE ALTMAS SEGNAME=PLANT,SEGTYPE=S0
-WRITE ALTMAS FIELDNAME=PLANTNAME,ALIAS=PLNTNM,FORMAT=A15,ACTUAL=A15,$
-WRITE ALTMAS SEGNAME=MEASURES,SEGTYPE=S0,PARENT=PLANT,OCCURS=16,$
-WRITE ALTMAS FIELDNAME=COLHDR,ALIAS=CH,FORMAT=A20,ACTUAL=A20,$
-WRITE ALTMAS FIELDNAME=RETURNS,ALIAS=RTN,FORMAT=P8C,ACTUAL=A8,$
-WRITE ALTMAS FIELDNAME=PROFIT,ALIAS=PRF,FORMAT=P20C,ACTUAL=A20,$
-WRITE ALTMAS FIELDNAME=COLCNTR,ALIAS=ORDER,FORMAT=I4,ACTUAL=I4,$
APP FI ALTMAS DISK hold.ftm
DEFINE FILE ALTMAS
BLANK/A5=' ';
END
TABLE FILE ALTMAS
SUM  RETURNS AS 'Returns'
OVER PCT.RETURNS/F5.1% AS '% of Total'
OVER PROFIT AS 'Profit'
OVER PCT.PROFIT/F5.1% AS '% of Total'
BY PLANTNAME NOPRINT
BY BLANK AS ''
ACROSS COLCNTR NOPRINT
ACROSS COLHDR AS ''
ON PLANTNAME SUBHEAD
"<PLANTNAME"
ON TABLE SET PAGE NOPAGE
END

You should be able to run this at your site. The trick is to create a bucket for each column on the report. It also uses -REPEAT loops and Dialogue Manager indexing to get the right column headers.

Thanks to all for your responses! I will give these try.

Andy

This is also a way to do it...
( just a sample, needs some work, but you'll get the idea )

-* first get all the data
TABLE FILE whatever
SUM data
BY dimension1
BY dimension2
BY period
WHERE period [select period that holds *all* other desired periodes ]
ON TABLE HOLD AS H_1 FORMAT FOCUS
END

-* smart matching per period
MATCH FILE H_1
SUM data AS 'data_a'
BY dimension1
BY dimension2
WHERE period [select period a ]
RUN
FILE H_1
SUM data AS 'data_b'
BY dimension1
BY dimension2
WHERE period [select period b ]
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE H_1
SUM data AS 'data_c'
BY dimension1
BY dimension2
WHERE period [ select period c]
AFTER MATCH HOLD OLD-OR-NEW
END

pro's:

Now you have a file with any desired dimensions and 3 seperate data fields:
• data_a
• data_b
• data_c

Good thing about different fields is that you can easily use them in computed to compare them.
i.e. compute DIFF/D5.2 = ( data_b - data_a )

con's:
depending on your database the first query might take more time collecting the total data.
but in our peticular case this is faster than 3 seperated queries and then matching them together.
( depends on which index / statistics you have on which dimensions )

Good luck,

One key thing to remember, I do not know what the limit is for the # of across items are, If you hit that limit you might have to create a temporary focus database and store the values in it to build you complete list of values. Then you could report off the temporary focus database.

Here's another example using OVER. Like Ginny, I used Dialogue Manager to calculate dates for the date ranges to avoid having to make these calculations for every record in the table. I used a week range beginning Monday and ending Sunday. You may need to change this depending on your requirements. Some of the styling will work only in HTML output (e.g., use of BR tag in Title DEFINES to force a line break).

-DEFAULTS &CURRDATE  = &YYMD.EVAL
-SET &DOW_NAME       = DOWK(&CURRDATE,'A3');
-SET &BACK_DAYS      = DECODE &DOW_NAME ('MON' 1 'TUE' 2 'WED' 3 'THU' 4 'FRI' 5 'SAT' 6 'SUN' 0 ELSE 0 );
-SET &PRIOR_SUNDAY_1 = AYMD(&CURRDATE,-&BACK_DAYS,'I8');
-SET &PRIOR_MONDAY_1 = AYMD(&PRIOR_SUNDAY_1,-6,'I8');
-SET &PRIOR_SUNDAY_2 = AYMD(&PRIOR_SUNDAY_1,-7,'I8');
-SET &PRIOR_MONDAY_2 = AYMD(&PRIOR_MONDAY_1,-7,'I8');
-SET &PRIOR_SUNDAY_3 = AYMD(&PRIOR_SUNDAY_1,-14,'I8');
-SET &PRIOR_MONDAY_3 = AYMD(&PRIOR_MONDAY_1,-14,'I8');
-SET &WEEK_52_SUNDAY = AYMD(&PRIOR_SUNDAY_1,-364,'I8');
-SET &WEEK_52_MONDAY = AYMD(&PRIOR_MONDAY_1,-364,'I8');
-SET &CURRYEAR       = EDIT(&CURRDATE,'9999$$$$');
-SET &FIRST_OF_YEAR  = &CURRYEAR || '0101';
-*
DEFINE FILE <filename>
 DUMMY/A1 = ' ';
-*
 WEIGHT1/D12.2 = IF <date col> FROM '&PRIOR_MONDAY_1' TO '&PRIOR_SUNDAY_1' THEN WEIGHT ELSE 0 ;
 WEIGHT2/D12.2 = IF <date col> FROM '&PRIOR_MONDAY_2' TO '&PRIOR_SUNDAY_2' THEN WEIGHT ELSE 0 ;
 WEIGHT3/D12.2 = IF <date col> FROM '&PRIOR_MONDAY_3' TO '&PRIOR_SUNDAY_3' THEN WEIGHT ELSE 0 ;
 WEIGHT4/D12.2 = IF <date col> FROM '&WEEK_52_MONDAY' TO '&WEEK_52_SUNDAY' THEN WEIGHT ELSE 0 ;
 WEIGHT5/D12.2 = IF <date col> FROM '&FIRST_OF_YEAR'  TO '&PRIOR_SUNDAY_1' THEN WEIGHT ELSE 0 ;
-*
 MILES1/D12.2  = IF <date col> FROM '&PRIOR_MONDAY_1' TO '&PRIOR_SUNDAY_1' THEN MILES ELSE 0 ;
 MILES2/D12.2  = IF <date col> FROM '&PRIOR_MONDAY_2' TO '&PRIOR_SUNDAY_2' THEN MILES ELSE 0 ;
 MILES3/D12.2  = IF <date col> FROM '&PRIOR_MONDAY_3' TO '&PRIOR_SUNDAY_3' THEN MILES ELSE 0 ;
 MILES4/D12.2  = IF <date col> FROM '&WEEK_52_MONDAY' TO '&WEEK_52_SUNDAY' THEN MILES ELSE 0 ;
 MILES5/D12.2  = IF <date col> FROM '&FIRST_OF_YEAR'  TO '&PRIOR_SUNDAY_1' THEN MILES ELSE 0 ;
-*
 COST1/D12.2   = IF <date col> FROM '&PRIOR_MONDAY_1' TO '&PRIOR_SUNDAY_1' THEN COST ELSE 0 ;
 COST2/D12.2   = IF <date col> FROM '&PRIOR_MONDAY_2' TO '&PRIOR_SUNDAY_2' THEN COST ELSE 0 ;
 COST3/D12.2   = IF <date col> FROM '&PRIOR_MONDAY_3' TO '&PRIOR_SUNDAY_3' THEN COST ELSE 0 ;
 COST4/D12.2   = IF <date col> FROM '&WEEK_52_MONDAY' TO '&WEEK_52_SUNDAY' THEN COST ELSE 0 ;
 COST5/D12.2   = IF <date col> FROM '&FIRST_OF_YEAR'  TO '&PRIOR_SUNDAY_1' THEN COST ELSE 0 ;
-*
 TITLE1/A25 = '<BR>Previous Week';
 TITLE2/A25 = '<BR>Two Weeks Ago';
 TITLE3/A25 = '<BR>Three Weeks Ago';
 TITLE4/A25 = 'Same Week<BR>Last Year';
 TITLE5/A25 = '<BR>YTD Total';
END
-*
TABLE FILE <filename>
 SUM WEIGHT1 AS 'Weight'
     WEIGHT2 AS ''
     WEIGHT3 AS ''
     WEIGHT4 AS ''
     WEIGHT5 AS ''
     OVER
     MILES1 AS 'Miles'
     MILES2 AS ''
     MILES3 AS ''
     MILES4 AS ''
     MILES5 AS ''
     OVER
     COST1 AS 'Cost'
     COST2 AS ''
     COST3 AS ''
     COST4 AS ''
     COST5 AS ''
 BY DUMMY NOPRINT
 ON DUMMY SUBHEAD
  " <TITLE1<TITLE2<TITLE3<TITLE4<TITLE5"
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
  TYPE=REPORT, GRID=ON, $
  TYPE=TITLE, STYLE=BOLD, $
  TYPE=SUBHEAD, HEADALIGN=BODY, STYLE=BOLD, $
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

You could replace:

-DEFAULTS &CURRDATE  = &YYMD.EVAL
-SET &DOW_NAME       = DOWK(&CURRDATE,'A3');
-SET &BACK_DAYS      = DECODE &DOW_NAME ('MON' 1 'TUE' 2 'WED' 3 'THU' 4 'FRI' 5 'SAT' 6 'SUN' 0 ELSE 0 );
-SET &PRIOR_SUNDAY_1 = AYMD(&CURRDATE,-&BACK_DAYS,'I8');

with

-SET &PRIOR_SUNDAY_1 = AYMD(&YYMD,-&DATEW,'I8');

Correct ... but I chose to make the code more flexible in case today's date would not be the starting date.