Focal Point
[SOLVED] RECOMPUTE TOTALS FOR EACH MONTH

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3307087016

October 07, 2011, 08:19 AM
souji
[SOLVED] RECOMPUTE TOTALS FOR EACH MONTH
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'
...




Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
October 10, 2011, 02:43 AM
souji
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 GM_LY_TOT_SHTG_A/P15.1CB=LY_TOT_SHTG_A/1000000; NOPRINT
COMPUTE GM_LY_BTWN_SLS_A/P15.1CB=LY_BTWN_SLS_A/1000000; NOPRINT

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.


WebFocus 7.1.4 xp html,excel,pdf