Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] RECOMPUTE TOTALS FOR EACH MONTH

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] RECOMPUTE TOTALS FOR EACH MONTH
 Login/Join
 
Silver Member
posted
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
 
Posts: 44 | Registered: February 27, 2008Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Silver Member
posted Hide Post
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
 
Posts: 44 | Registered: February 27, 2008Report This Post
Gold member
posted Hide Post
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
 
Posts: 65 | Registered: July 28, 2011Report This Post
Silver Member
posted Hide Post
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
 
Posts: 44 | Registered: February 27, 2008Report This Post
Gold member
posted Hide Post
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
 
Posts: 58 | Location: Sydney, Australia | Registered: April 22, 2005Report This Post
Silver Member
posted Hide Post
Thanks every one.I used Multiverb and displayed subtotals.


WebFocus 7.1.4 xp html,excel,pdf
 
Posts: 44 | Registered: February 27, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] RECOMPUTE TOTALS FOR EACH MONTH

Copyright © 1996-2020 Information Builders