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     Conditional ROW or ACROSS-TOTAL Solved

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Conditional ROW or ACROSS-TOTAL Solved
 Login/Join
 
Master
posted
I'm probably just not thinking this through, but I'm on the 29th day of a sinus headache and my ability to think thing through is in the crapper.

Our fiscal year runs from Oct 1 to Sept 30 the next year. I have an application that needs to show sales by month and total for fiscal year to date. Except in Oct I am showing all month of the prior YTD plus Oct. ROW-TOTAL or ACROSS-TOTAL will work fine except for Oct.

 
TABLE FILE myfile
SUM NET_REVENUE
BY DIVISION NOPRINT
ON DIVISION SUBTOTAL AS 'Total Region'
ON DIVISION SUBFOOT
" "
BY CC AS 'Cost,Center'
ACROSS YRMO AS ''
WHERE DOCUMENT_COMPANY EQ  '&CO.EVAL'
WHERE G_L_DATE_FOR GE '&STRTYYMD.EVAL'
 AND G_L_DATE_FOR LE '&ENDYYMD.EVAL'

HEADING
"<+0>Sales Fiscal YTD"
ON TABLE COLUMN-TOTAL AS 'Total for Month'
ON BUSINESS_UNIT ROW-TOTAL AS 'Total YTD'
ON TABLE PCHOLD FORMAT EXL97
END
 

This message has been edited. Last edited by: PBrightwell,


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Pat,

Your sinus headache is very acute.

Is it that you want to have a row-total only if there is more than 1 month?

In your code you have ON BUSINESS_UNIT ROW-TOTAL and BUSINESS_UNIT is not used in your TABLE request. Also ROW-TOTAL only works with ON TABLE.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
No, In October the report will show all of the previous fiscal year Oct 200x thru Sep 200x+1 and Oct 200x+1, I don't want Oct 200x+1 in the total YTD for the previous fiscal year. In Nov the report will show all of Oct and Nov to date.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
Pat, you need better drugs if you been hurting for 29 days! Eeker

I suspect, though I can't tell because you didn't post your date Dialogue Manager code, that the issue is with your date compares. You might have to do a LT instead of a LE on the end date or do special processing if it is the first month of the new fiscal year.

Check into that and let us know how it goes.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Master
posted Hide Post
They gave me better drugs yesterday, I was a zombie all afternoon and awake all night! And the headache is coming back! Maybe that's why I am not explaining this well.

On July 25, 2008, the report will display Oct 2007 thru July 25, 2008 and if I ROW-TOTAL the YTD is correct.

On Oct 25, 2008 I want this report to display Oct 1, 2007 thru Oct 25, 2008. But the YTD figure should be Oct 1, 2007 thru Sep 30, 2008 AND Oct 1 - 25 should display as a column not included in that total.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Would this be satisfactory? In the bridge month, it pushes October of the new fiscal year onto a second line.
-*create sample data for 10/2006-10/2007:
SET HOLDLIST=PRINTONLY
TABLE FILE CAR 
  PRINT SALES/D10 
  COMPUTE MONTH/MTY= IF MONTH EQ 0 THEN 'OCT 2006' ELSE MONTH+1; 
  BY TOTAL MONTH
  IF SALES NE 0
  ON TABLE HOLD
END
-* report each fiscal year as a separate line, with row-total
DEFINE FILE HOLD
  YR/YY=MONTH;
  MO/MT=MONTH;
  FY/YY=YR + (MO GT 'SEP');
END
TABLE FILE HOLD
  WRITE SALES 
  BY FY
    ACROSS MONTH 
  ON TABLE ROW-TOTAL
  IF MONTH FROM 'OCT 2006' TO 'OCT 2007'
END 

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
I think Jack has your solution. That is what I would recommend. Keep your fiscal years separate by giving them a number and sorting on it.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
Pat,

This may also be a solution to what you want to do.

DEFINE FILE EMPDATA
THEYYM/YYM=HIREDATE;
THEYY/YY =HIREDATE;
THEMM/M =HIREDATE;
THEFY/YY =IF (THEMM FROM 10 TO 12) THEN THEYY + 1 ELSE THEYY;
END

TABLE FILE EMPDATA
SUM SALARY
BY DIV
ACROSS THEFY NOPRINT
ACROSS THEYY AS '' ACROSS-TOTAL
ACROSS THEMM AS ''
WHERE THEYYM FROM '1989/10' TO '1990/10'
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Virtuoso
posted Hide Post
Depending on where you want the FY Total column the following may also be a solution.

DEFINE FILE EMPDATA
THEYYM/YYM=HIREDATE;
THEYY/YY =HIREDATE;
THEMM/Mt =HIREDATE;
THEFY/YY =IF (THEMM FROM 10 TO 12) THEN THEYY + 1 ELSE THEYY;
FYSALTOT =IF (THEFY EQ '1990') THEN SALARY ELSE 0;
END

TABLE FILE EMPDATA
SUM FYSALTOT NOPRINT
BY DIV
SUM SALARY
BY DIV
ACROSS THEFY AS 'Fiscal Year'
COMPUTE FYSAL/D12M=FYSALTOT;
ACROSS THEYY NOPRINT
ACROSS THEMM AS ''
WHERE THEYYM FROM '1989/10' TO '1990/10'
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Master
posted Hide Post
I worked it out, and amazingly my solution is very similar to Mickey's. I put the Fiscal Year figure first. I am waiting on a reply from the user, but I think he will be pleased.

for anyone needing a solution, here's the final code

 

DEFINE FILE myfile
NET_REVENUE/D15.2=NET_REVENUE/100;
YRMO/A6YYMT  =EDIT(G_L_DATE_FOR,'999999$$$') ;
FISCYTD/D15.2=IF G_L_DATE_FOR LE '&FISCEND' THEN NET_REVENUE ELSE 0;
-*
BUSINESS_UNIT/A4=EDIT(I1EMCU,'$$$$$$$$9999');
CC/A4=BUSINESS_UNIT;
ALL/A72=DECODE CC
('   ' 'UNIDENTIFIED        '
 '4102' 'TOLL BLENDING - INTERCOMPANY                               '
 '4104' 'OTHER                                                      '
 '4119' 'PACIFIC COAST REGION                                       '
 '4120' 'REMEDIAL SERVICES                                          '
 '4121' 'REMEDIAL SERVICES                                          '
 '4150' 'INTERNATIONAL SALES                                        '
 '4158' 'INTERNATIONAL SALES                                        '
 '4170' 'INTERNATIONAL SALES                                        '
 '4161' 'INTERNATIONAL SALES                                        '
 '4162' 'INTERNATIONAL SALES                                        '
 '4163' 'INTERNATIONAL SALES                                        '
 '4164' 'INTERNATIONAL SALES                                        '
 '4165' 'INTERNATIONAL SALES                                        '
 '4166' 'INTERNATIONAL SALES                                        '
 '4167' 'INTERNATIONAL SALES                                        '
 '4168' 'INTERNATIONAL SALES                                        '
 '4170' 'INTERNATIONAL SALES                                        '
 '4170' 'INTERNATIONAL SALES                                        '
 '4171' 'INTERNATIONAL SALES                                        '
 '4177' 'INTERNATIONAL SALES                                        '
 '4207' 'ROCKY MOUNTAIN REGION                                      '
 '4211' 'INDUSTRIAL GROUP                                           '
 '4212' 'INDUSTRIAL GROUP                                           '
 '4218' 'INDUSTRIAL GROUP                                           '
 '4219' 'INDUSTRIAL GROUP                                           '
 '4236' 'PERMIAN BASIN REGION                                       '
 '4240' 'INDUSTRIAL GROUP                                           '
 '4249' 'CANADA                                                     '
 '4260' 'ROCKY MOUNTAIN REGION                                      '
 '4272' 'INDUSTRIAL GROUP                                           '
 '4291' 'INDUSTRIAL GROUP                                           '
 '4292' 'EAST REGION                                                '
 '4293' 'EAST REGION                                                '
 '4301' 'DIVISION/REGION SALES                                      '
 '4305' 'EAST REGION                                                '
 '4306' 'EAST REGION                                                '
 '4310' 'DIVISION/REGION SALES                                      '
 '4311' 'DIVISION/REGION SALES                                      '
 '4312' 'DIVISION/REGION SALES                                      '
 '4313' 'DIVISION/REGION SALES                                      '
 '4314' 'DIVISION/REGION SALES                                      '
 '4315' 'PERMIAN BASIN REGION                                       '
 '4316' 'DIVISION/REGION SALES                                      '
 '4317' 'DIVISION/REGION SALES                                      '
 '4318' 'DIVISION/REGION SALES                                      '
 '4319' 'DIVISION/REGION SALES                                      '
 '4330' 'PERMIAN BASIN REGION                                       '
 '4334' 'ROCKY MOUNTAIN REGION                                      '
 '4337' 'ROCKY MOUNTAIN REGION                                      '
 '4341' 'PERMIAN BASIN REGION                                       '
 '4380' 'ROCKY MOUNTAIN REGION                                      '
 '4409' 'EAST REGION                                                '
 '4420' 'PERMIAN BASIN REGION                                       '
 '4530' 'ROCKY MOUNTAIN REGION                                      '
 '4620' 'EAST REGION                                                '
 '4621' 'EAST REGION                                                '
 '4720' 'PACIFIC COAST REGION                                       '
 '4740' 'PACIFIC COAST REGION                                       '
 '4750' 'PACIFIC COAST REGION                                       ') ;
 DIVISION/A50=EDIT
(ALL,'999999999999999999999999999999999999999$$$');
-*REGION/A3=EDIT(BUSINESS_UNIT,'999');
END
-RUN

-*-INCLUDE st924as
-*
TABLE FILE myfile
SUM FISCYTD AS 'Total Fisc Year,&STRTYYMD.EVAL,to &ENDYYMD.EVAL'
BY DIVISION
BY CC
SUM NET_REVENUE AS 'Net,Revenue'
 
ACROSS YRMO AS ''

BY DIVISION NOPRINT
BY CC AS 'Cost,Center'
ON DIVISION SUBTOTAL AS 'Total Region'
ON DIVISION SUBFOOT
" "
WHERE DOCUMENT_COMPANY EQ  '00115'
WHERE G_L_DATE_FOR GE '&STRTYYMD.EVAL'
 AND G_L_DATE_FOR LE '&ENDYYMD.EVAL'

HEADING
"<+0>BJ Chemical Services "
"<+0>Sales Fiscal YTD"
ON TABLE COLUMN-TOTAL AS 'Total for Month'


ON TABLE PCHOLD FORMAT EXL97
END
 


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Pat

I see that your fiscal year total now appears in the first column. If you want that this is ok.
If you want it in the last column it would be possible too.
Put a noprint after the "SUM FISCYTD" and then after the across a COMPUTE NEWFIS/D15.2=FISCYTD;

There are some examples on this forum. Search on "fiscal year"...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
Pat,

You have had many responses, so mine is a bit redundant. Nonetheless (I am using GGSALES for the example and I divided the DOLLARS field by 100000 to make the result more readable) :
  
-* File Pat1.fex
-SET &ECHO=ALL;
-SET &FISC='1996';
-SET &NXTFISC=&FISC + 1;
-SET &FMON='10/' | &FISC;
-SET &LMON='09/' | &NXTFISC;
-SET &TMON='01/1997';
-RUN
DEFINE FILE GGSALES
IDATE/YYMD=DATE;
IMONTH/MYY=IDATE;
FYEAR/YY=IF IMONTH LE '&LMON' THEN &FISC ELSE &NXTFISC;
FDOLLARS/D6.2=IF FYEAR EQ &FISC THEN DOLLARS/100000 ELSE 0;
END
-RUN
-REPEAT #YTD FOR &I FROM 1 TO 3;
TABLE FILE GGSALES
SUM FDOLLARS NOPRINT
BY REGION
SUM COMPUTE DOLLARS=DOLLARS/100000; AS Dollars
BY REGION
ACROSS IMONTH COMPUTE TOTFYEAR/D6.2=C1;
WHERE IMONTH FROM '&FMON' TO '&TMON'
END
-SET &TMON=IF &I EQ 1 THEN '09/1997' ELSE '11/1997';
-#YTD


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
quote:
Put a noprint after the "SUM FISCYTD" and then after the across a COMPUTE NEWFIS/D15.2=FISCYTD;


Frank,

I considered doing exactly this, but then when I have the 13th month my fiscal year total would appear after it but would not include it. I thought that would be more confusing for my user than putting the fiscal YTD first and labeling the dates in the title.

Thanks to everyone for your input. Hopefully, the headache is history.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Pat,

Now that you have a solution, please edit your original post and change the SUBJECT TITLE to include [SOLUTION] or [SOLVED]. this will help eveyone to know there is a solution in the thread.

For example:

[SOLVED] Conditional ROW or ACROSS-TOTAL


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Virtuoso
posted Hide Post
quote:
the headache is history



Cool

hope so too...this one anyway...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report 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     Conditional ROW or ACROSS-TOTAL Solved

Copyright © 1996-2020 Information Builders