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,

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.

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, 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.

Check into that and let us know how it goes.

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.

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,

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.

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

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

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

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"...

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

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,

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

quote:

the headache is history

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