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
July 25, 2008, 09:27 AM
Danny-SRL
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
July 25, 2008, 11:38 AM
PBrightwell
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
July 25, 2008, 12:00 PM
GinnyJakes
Pat, you need better drugs if you been hurting for 29 days!
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.
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
July 25, 2008, 01:48 PM
j.gross
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
July 25, 2008, 02:28 PM
GinnyJakes
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.
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
July 25, 2008, 03:29 PM
mgrackin
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
July 25, 2008, 03:51 PM
PBrightwell
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
July 25, 2008, 04:52 PM
FrankDutch
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
July 27, 2008, 02:32 PM
Danny-SRL
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
July 28, 2008, 08:42 AM
PBrightwell
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
July 28, 2008, 09:22 AM
mgrackin
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
July 28, 2008, 02:38 PM
FrankDutch
quote:
the headache is history
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