I have areport with all Week information,month information. sample report is
PRINT SHTG SALES BY ACCT_YR_I BY DUMMY NOPRINT BY ACCT_MO_I BY V_BLANK NOPRINT BY V_WK_N AS 'ACCT, WK' ACROSS V_GROUP AS '' ON V_BLANK RECOMPUTE AS 'MTD'
output is Acct week SHTG $ SALES $
Feb Wk2 21.7 1,828.2 Feb Wk3 24.2 2,083.4 Feb Wk4 19.9 1,705.8
MTD 65.7 5,617.3 YTD 65.7 5,617.3
Mar Wk1 21.0 1,961.0 Mar Wk2 21.9 1,851.9 Mar Wk3 19.9 1,620.2 Mar Wk4 20.6 1,715.3 Mar Wk5 18.4 1,603.6
MTD 101.9 8,752.0 YTD 167.6 14,369.3
From the above report how to get YTD information. MTD is a recompute value.March ytd is addition of FEB MTD and MARCH MTD.and so on....
Thanks in advance.This message has been edited. Last edited by: Kerry,
WebFocus 7.1.4 xp html,excel,pdf
October 07, 2011, 01:57 PM
njsden
quote:
ON V_BLANK RECOMPUTE AS 'MTD'
If that works for your monthly breakdown, then this should for the yearly total:
...
ON TABLE SUMMARIZE AS 'YTD'
...
I don't see any COMPUTE's in your request so why do you need RECOMPUTE? a simpler SUBTOTAL would suffix and in that case:
...
ON V_BLANK SUBTOTAL AS 'MTD'
ON TABLE COLUMN-TOTAL AS 'YTD'
...
sorry actually my print fields are compute fields. For every month we are displayinf MTD and YTD values.In my previous post i mentioned 2 months report.If i give subtotal or column-total it will be for entire report ytd total it will display.We want for every month YTD information.
WebFocus 7.1.4 xp html,excel,pdf
October 10, 2011, 07:51 AM
ReddyP
Hello Souji, Another alternative would be to do all the totals in the computes and put them in a sub foot. Personally, I do not like this because you would have to play with the formatting a bit to align correctly.
7.7.01,windows2008 R2
October 11, 2011, 08:06 AM
souji
yes my last option is subfoot. OUTPUT IS
ACCTWK STATUS SHTG $ SALES $ SHTG % B/(P)LY
Feb Wk 2 FB 21.7 1,828.2 1.19 (0.01) Feb Wk 3 FB 24.2 2,083.4 1.16 0.03 Feb Wk 4 FB 19.9 1,705.8 1.16 0.13 MTD 65.7 5,617.3 1.17 0.05 YTD 65.7 5,617.3 1.17 0.05 Mar Wk 1 FB 21.0 1,961.0 1.07 0.03 Mar Wk 2 FB 21.9 1,851.9 1.18 (0.01) Mar Wk 3 FB 19.9 1,620.2 1.23 (0.02) Mar Wk 4 FB 20.6 1,715.3 1.20 0.08 Mar Wk 5 FB 18.4 1,603.6 1.15 0.02 MTD 101.9 8,752.0 1.16 0.02 YTD 167.6 14,369.3 1.17 0.03 . . . . Dec Wk1
MY CODE IS
TABLE FILE TOTSHT PRINT COMPUTE GM_WTD_TOT_SHTG_A/P15.1CB=WTD_TOT_SHTG_A/1000000; AS 'SHTG $' COMPUTE GM_WTD_BTWN_SLS_A/P15.1CB=WTD_BTWN_SLS_A/1000000; AS 'SALES $'
COMPUTE CY_RATE/P15.2CB=(GM_WTD_TOT_SHTG_A/GM_WTD_BTWN_SLS_A)*100; AS 'SHTG %' COMPUTE LY_RATE/P15.2CB=(GM_LY_TOT_SHTG_A/GM_LY_BTWN_SLS_A)*100; NOPRINT
COMPUTE BP_LY/P15.2CB=((GM_LY_TOT_SHTG_A/GM_LY_BTWN_SLS_A)*100)-((GM_WTD_TOT_SHTG_A/GM_WTD_BTWN_SLS_A)*100); AS 'B/(P),LY'
BY ACCT_YR_I NOPRINT BY DUMMY NOPRINT BY ACCT_MO_I NOPRINT BY V_BLANK NOPRINT BY V_WK_N AS 'ACCT, WK' BY SEG_STAT_C AS 'INV,STATUS' ACROSS V_GROUP1 AS '' ON V_BLANK RECOMPUTE AS 'MTD' ON V_BLANK SUBFOOT AS "END -RUN
I WAS ABLE TO GET MTD VALUES correct. when i tried to print the YTD values by using subfoot SHTG $ and SALES $ are coming correct.Remaining 2 SHTG% and B/(P)LY are wrong. Can some one please suggest any idea.
YTD SHTG% is average of FEB MTD value and MAR MTD value.
YTD value is average of MTD values.
Thanks in advance.
WebFocus 7.1.4 xp html,excel,pdf
October 19, 2011, 08:42 PM
Mika Keranen
Hi Souji,
How about using a multiverb request and displaying the totals in a SUBFOOT? Making the SUBFOOT aling correctly is not too hard a task.
Cheers, Mika
DEFINE FILE GGSALES
YEAR/YY = DATE ;
MONTH/M = DATE ;
END
-SET &NOPRINT = 'NOPRINT' ;
-*-SET &NOPRINT = '' ;
SET LINES = 999999
TABLE FILE GGSALES
SUM
COMPUTE MTD_DOLLARS/D8 = DOLLARS ; &NOPRINT
COMPUTE YTD_DOLLARS/D8 = YTD_DOLLARS + MTD_DOLLARS ; &NOPRINT
COMPUTE MTD_BUDDOLLARS/D8 = BUDDOLLARS ; &NOPRINT
COMPUTE YTD_BUDDOLLARS/D8 = YTD_BUDDOLLARS + MTD_BUDDOLLARS ; &NOPRINT
COMPUTE MTD_PCT/D7.2 = MTD_DOLLARS / MTD_BUDDOLLARS * 100 ; &NOPRINT
COMPUTE YTD_PCT/D7.2 = YTD_DOLLARS / YTD_BUDDOLLARS * 100 ; &NOPRINT
BY MONTH
SUM DOLLARS/D8
BUDDOLLARS/D8
COMPUTE PCT/D7.2 = DOLLARS / BUDDOLLARS * 100 ;
BY MONTH
BY ST
WHERE YEAR EQ 1997 ;
ON MONTH SUBFOOT
" <+0>MTD <MTD_DOLLARS<MTD_BUDDOLLARS<MTD_PCT"
" <+0>YTD <YTD_DOLLARS<YTD_BUDDOLLARS<YTD_PCT"
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, HEADALIGN=BODY, $
TYPE=REPORT, GRID=OFF, FONT=VERDANA, SIZE=8, $
TYPE=SUBFOOT, STYLE=BOLD, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, ITEM=2, JUSTIFY=LEFT, $
ENDSTYLE
END
WebFOCUS 7.6.x PMF 5.2.x
October 21, 2011, 04:25 AM
souji
Thanks every one.I used Multiverb and displayed subtotals.