Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Product1 2016 24 19 17 26 32 26 31 29 25 30 27 16 product1 2017 19 23 24 21 24 24 22 24 Percent Change -20.8% 21.1% 41.2% -19.2% -25.0% -7.7% -29.0% -17.2% Product2 2016 46 40 60 61 44 42 68 49 41 50 44 47 Product2 2017 58 39 22 41 42 34 40 34 Percent Change 26.1% -2.5% -63.3% -32.8% -4.5% -19.0% -41.2% -30.6% Product3 2016 264 172 227 246 217 234 276 302 277 241 254 255 product3 2017 228 173 223 197 201 217 256 251 Percent Change -13.6% 0.6% -1.8% -19.9% -7.4% -7.3% -7.2% -16.9%
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Product1 2016 24 19 17 26 32 26 31 29 25 30 27 16 product1 2017 19 23 24 21 24 24 22 24 Product2 2016 46 40 60 61 44 42 68 49 41 50 44 47 Product2 2017 58 39 22 41 42 34 40 34 Product3 2016 264 172 227 246 217 234 276 302 277 241 254 255 product3 2017 228 173 223 197 201 217 256 251
DEFINE FILE GGSALES YR /YY = DATE; MM /MONTH = DATE; END TABLE FILE GGSALES SUM DOLLARS COMPUTE VAL/A15V = FPRINT(DOLLARS, 'I08', 'A15V'); BY CITY BY YR BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR); BY MM ON TABLE HOLD AS EXTDATA END -RUN DEFINE FILE EXTDATA SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0; SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0; END TABLE FILE EXTDATA SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT COMPUTE VAL/A15V = FPRINT(PCT, 'P8.2C%', 'A15V'); BY CITY BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change'; BY MM ON TABLE HOLD AS PCTDATA END -RUN TABLE FILE EXTDATA SUM VAL AS '' BY CITY AS '' BY YR NOPRINT BY ROWLABEL AS '' ACROSS MM AS '' MORE FILE PCTDATA END -RUN
DEFINE FILE GGSALES YR /YY = DATE; MONTH_NUM /MONTH = DATE; MM/A4 = DECODE MONTH_NUM(01 Jan 02 Feb 03 Mar 04 Apr 05 Mar 06 Jun 07 Jul 08 Aug 09 Sep 10 Oct 11 Nov 12 Dec); END TABLE FILE GGSALES SUM DOLLARS COMPUTE VAL/A15V = FPRINT(DOLLARS, 'I08', 'A15V'); BY CITY BY YR BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR); BY MM BY MONTH_NUM ON TABLE HOLD AS EXTDATA END -RUN DEFINE FILE EXTDATA SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0; SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0; END TABLE FILE EXTDATA SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT COMPUTE VAL/A15V = FPRINT(PCT, 'P8.2C%', 'A15V'); BY CITY BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change'; BY MONTH_NUM BY MM ON TABLE HOLD AS PCTDATA END -RUN TABLE FILE EXTDATA SUM VAL AS '' BY CITY AS '' BY YR NOPRINT BY ROWLABEL AS '' ACROSS MONTH_NUM NOPRINT ACROSS MM AS '' MORE FILE PCTDATA END -RUN
quote:
I was heading down this road also. But since you did such a good job I can only add this tiny improvement
...
MM/A4 = DECODE MONTH_NUM(01 Jan 02 Feb 03 Mar 04 Apr 05 Mar 06 Jun 07 Jul 08 Aug 09 Sep 10 Oct 11 Nov 12 Dec);
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Product1 2016 24 19 17 26 32 26 31 29 25 30 27 16 302 product1 2017 19 23 24 21 24 24 22 24 181 Percent Change -20.8% 21.1% 41.2% -19.2% -25.0% -7.7% -29.0% -17.2% Product2 2016 46 40 60 61 44 42 68 49 41 50 44 47 Product2 2017 58 39 22 41 42 34 40 34 Percent Change 26.1% -2.5% -63.3% -32.8% -4.5% -19.0% -41.2% -30.6% Product3 2016 264 172 227 246 217 234 276 302 277 241 254 255 product3 2017 228 173 223 197 201 217 256 251 Percent Change -13.6% 0.6% -1.8% -19.9% -7.4% -7.3% -7.2% -16.9% Total 2016 : Total 2017 :
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
DEFINE FILE GGSALES YR /YY = DATE; MM /MONTH = DATE; END TABLE FILE GGSALES SUM DOLLARS COMPUTE VAL/12.2 = FPRINT(DOLLARS, 'I08', 'A15V'); BY CITY BY YR BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR); BY MM ON TABLE HOLD AS EXTDATA END -RUN DEFINE FILE EXTDATA SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0; END TABLE FILE EXTDATA COMPUTE VAL/D12.2 = SALES1 BY CITY BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL/A20V = 'Total 2016'; BY MM ON TABLE HOLD AS PCTDATA1 END -RUN DEFINE FILE EXTDATA SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0; END TABLE FILE EXTDATA COMPUTE VAL/D12.2 = SALES2 BY CITY BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL/A20V = 'Total 2017'; BY MM ON TABLE HOLD AS PCTDATA2 END -RUN DEFINE FILE EXTDATA SALES1 /D6 = IF YR EQ 1996 THEN DOLLARS ELSE 0; SALES2 /D6 = IF YR EQ 1997 THEN DOLLARS ELSE 0; END TABLE FILE EXTDATA SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT COMPUTE VAL/D12.2 = FPRINT(PCT, 'P8.2C%', 'A15V'); BY CITY BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change'; BY MM ON TABLE HOLD AS PCTDATA END -RUN TABLE FILE EXTDATA SUM VAL AS '' BY CITY AS '' BY YR NOPRINT BY ROWLABEL AS '' ACROSS MM AS '' ACROSS-TOTAL MORE FILE PCTDATA END -RUN
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Product1 2016 24 19 17 26 32 26 31 29 25 30 27 16 302 product1 2017 19 23 24 21 24 24 22 24 181 Percent Change -20.8% 21.1% 41.2% -19.2% -25.0% -7.7% -29.0% -17.2% xxxx Total 2016: Total 2017: Product2 2016 46 40 60 61 44 42 68 49 41 50 44 47 Product2 2017 58 39 22 41 42 34 40 34 Percent Change 26.1% -2.5% -63.3% -32.8% -4.5% -19.0% -41.2% -30.6% Total 2016: Total 2017: Product3 2016 264 172 227 246 217 234 276 302 277 241 254 255 product3 2017 228 173 223 197 201 217 256 251 Percent Change -13.6% 0.6% -1.8% -19.9% -7.4% -7.3% -7.2% -16.9% Total 2016 : Total 2017 :
quote:How can calculate for the entire year
TABLE FILE EXTDATA SUM COMPUTE VAL2/D12.2 = SALES1; -*BY TOTAL COMPUTE FILECLASSS = 9999; BY LOWEST FILE_CLASS_DASHBD_DESC; BY TOTAL COMPUTE Defyear/YY = 9999; BY TOTAL COMPUTE ROWLABEL/A20V = 'Total Violent 2016'; -*BY TOTAL COMPUTE ROWLABEL1/A20V = 'Total Violent 2016'; BY Defmonth ON TABLE HOLD AS PCDATA1 -*FORMAT FOCUS END -RUN
DEFINE FILE GGSALES YR /YY = DATE; MONTH_NUM /MONTH = DATE; MM_NO /D2 = MONTH_NUM; MM /A5V = DECODE MONTH_NUM(01 Jan 02 Feb 03 Mar 04 Apr 05 Mar 06 Jun 07 Jul 08 Aug 09 Sep 10 Oct 11 Nov 12 Dec); END TABLE FILE GGSALES SUM DOLLARS/P10C BY CITY BY YR BY MM_NO BY MM ON TABLE HOLD AS EXTDATA END -RUN TABLE FILE EXTDATA SUM DOLLARS COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 1; BY CITY BY YR BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR); BY MM_NO BY MM ON TABLE HOLD AS DETDATA END -RUN TABLE FILE EXTDATA SUM DOLLARS COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 1; BY CITY BY YR BY TOTAL COMPUTE ROWLABEL /A20V = 'Year ' | EDIT(YR); BY TOTAL COMPUTE MM_NO /D2 = 99; BY TOTAL COMPUTE MM /A5V = 'Total'; ON TABLE HOLD AS DETROWTOT END -RUN DEFINE FILE EXTDATA SALES1 /P10C = IF YR EQ 1996 THEN DOLLARS ELSE 0; SALES2 /P10C = IF YR EQ 1997 THEN DOLLARS ELSE 0; END TABLE FILE EXTDATA SUM SALES1 SALES2 COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT COMPUTE VAL/A15V = FPRINT(PCT, 'P8.2C%', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 1; BY CITY BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL/A20V = 'Percent Change'; BY MM_NO BY MM ON TABLE HOLD AS PCTDATA END -RUN TABLE FILE PCTDATA SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT COMPUTE VAL/A15V = FPRINT(PCT, 'P8.2C%', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 1; BY CITY BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL /A20V = 'Percent Change'; BY TOTAL COMPUTE MM_NO /D2 = 99; BY TOTAL COMPUTE MM /A5V = 'Total'; ON TABLE HOLD AS PCTROWTOT END -RUN TABLE FILE EXTDATA SUM DOLLARS COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 10; BY TOTAL COMPUTE CITY /A20 = 'Total'; BY YR BY TOTAL COMPUTE ROWLABEL/A20V = 'Year ' | EDIT(YR); BY MM_NO BY MM ON TABLE HOLD AS YRTOT END -RUN TABLE FILE YRTOT SUM DOLLARS COMPUTE VAL/A15V = FPRINT(DOLLARS, 'P10C', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 10; BY TOTAL COMPUTE CITY /A20 = 'Total'; BY YR BY TOTAL COMPUTE ROWLABEL /A20V = 'Year ' | EDIT(YR); BY TOTAL COMPUTE MM_NO /D2 = 99; BY TOTAL COMPUTE MM /A5V = 'Total'; ON TABLE HOLD AS YRROWTOT END -RUN DEFINE FILE YRTOT SALES1 /P10C = IF YR EQ 1996 THEN DOLLARS ELSE 0; SALES2 /P10C = IF YR EQ 1997 THEN DOLLARS ELSE 0; END TABLE FILE YRTOT SUM SALES1 SALES2 COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT COMPUTE VAL/A15V = FPRINT(PCT, 'P8.2C%', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 10; BY TOTAL COMPUTE CITY /A20 = 'Total'; BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL /A20V = 'Percent Change'; BY MM_NO BY MM ON TABLE HOLD AS YRTOTPCTDATA END -RUN TABLE FILE PCTDATA SUM COMPUTE PCT/P8.2C% = (SALES2 - SALES1) / SALES1 * 100; NOPRINT COMPUTE VAL/A15V = FPRINT(PCT, 'P8.2C%', 'A15V'); BY TOTAL COMPUTE ROWID /I2 = 10; BY TOTAL COMPUTE CITY /A20 = 'Total'; BY TOTAL COMPUTE YR/YY = 9999; BY TOTAL COMPUTE ROWLABEL /A20V = 'Percent Change'; BY TOTAL COMPUTE MM_NO /D2 = 99; BY TOTAL COMPUTE MM /A5V = 'Total'; ON TABLE HOLD AS YRTOTPCTTOT END -RUN TABLE FILE DETDATA SUM VAL BY ROWID BY CITY BY YR BY ROWLABEL BY MM_NO BY MM ON TABLE HOLD AS RPTDATA MORE FILE DETROWTOT MORE FILE PCTDATA MORE FILE PCTROWTOT MORE FILE YRTOT MORE FILE YRROWTOT MORE FILE YRTOTPCTDATA MORE FILE YRTOTPCTTOT END -RUN TABLE FILE RPTDATA SUM VAL AS '' BY ROWID NOPRINT BY CITY AS '' BY YR NOPRINT BY ROWLABEL AS '' ACROSS MM_NO NOPRINT ACROSS MM AS '' ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * TYPE=ACROSSVALUE, JUSTIFY=RIGHT, $ TYPE=DATA, ACROSSCOLUMN=VAL, JUSTIFY=RIGHT, $ END -RUN
COMPUTE DIFF/D12.2=FIELDNAME - LAST FIELDNAME;