I have a report that is setup similar to the example below. I need to calculate the difference between the two quarter total and then I need to calculate the change percentage. I figure if I can figure out how to calculate the difference the change percentage shouldn't be that hard. I tried using FML, but the FOR statement only allows one field and I need to have it group by two different fields with one of the fields being a NOPRINT subtotal. I'm building the report in App Studio.

DEFINE FILE GGSALES
DATE_YYMD/YYMD=DATECVT(DATE, 'I8YYMD', 'YYMD');
DATE_YYQ/YYQ=FIYYQ(DATE_YYMD, 'D', 1, 1, 'FYE', DATE_YYQ);
END
TABLE FILE GGSALES
SUM
     DOLLARS/P12.2CM
     AVE.DOLLARS/P12.2CM AS 'Avg Dollar Sales'
BY  LOWEST CATEGORY NOPRINT
BY  PCD
ACROSS LOWEST DATE_YYQ AS ''

ON CATEGORY SUBTOTAL AS ''
WHERE ( DATE GE 19960101 ) AND ( DATE LE 19960630 );
WHERE RECORDLIMIT EQ 5000
ON TABLE SET SUBTOTALS ABOVE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Any help would be greatly appreciated.

Thanks,

Ralph

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Ralph and welcome to the Forum.

Using your exact example, you can add COMPUTEs after the ACROSS verb to add columns to the right of your code but this may not be what you want. Especially as you (I am guessing) only want the difference and percentage at CATEGORY (SUBTOTAL) level?

Remember that the single value in the FOR syntax does not prevent you from using a compound value made from two or more sort values and that is probably the way that I would go - if I knew more about the required end result. I would also trap the SQL if this is against an RDBMS to ensure that it is efficient.

Anyway, try this version of your code. Note that the column notation (C1, C3 etc.) are not what you expect because of the dynamic reformatting and the resultant internal matrix created.

DEFINE FILE GGSALES
DATE_YYMD/YYMD=DATECVT(DATE, 'I8YYMD', 'YYMD');
DATE_YYQ/YYQ=FIYYQ(DATE_YYMD, 'D', 1, 1, 'FYE', DATE_YYQ);
END
TABLE FILE GGSALES
SUM
     DOLLARS/P12.2CM
     AVE.DOLLARS/P12.2CM AS 'Avg Dollar Sales'
BY  LOWEST CATEGORY NOPRINT
BY  PCD
ACROSS LOWEST DATE_YYQ AS ''
COMPUTE DOLL_QTRDIFF/P12.2CM = C1 - C5; AS 'Difference'
COMPUTE DOLL_QTRPCENT/D6.2% = (C1 - C5) / C1 * 100; AS '% Diff'
ON CATEGORY SUBTOTAL AS ''
WHERE ( DATE GE 19960101 ) AND ( DATE LE 19960630 );
WHERE RECORDLIMIT EQ 5000
ON TABLE SET SUBTOTALS ABOVE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Good luck

T

Thanks for the information Tony, it worked. I thought I tried using the C# and it didn't work. Maybe I had the compute statement in the wrong place. I did want the difference at the detail and subtotal level so this work. I think this will work for now. I may need to play with FML a little more and then may change this report to use FML.

Once agian, thanks.